Link to home
Start Free TrialLog in
Avatar of freshwaterwest
freshwaterwestFlag for United Kingdom of Great Britain and Northern Ireland

asked on

delete higher id rows where column has two same

I have some additional rows that have been created accidentally and are causing problems in modx manager. I need to find and remove but only remove the higher id for example.

Here's my select to find the duplicate rows (there should only be one contentid for each tmplvarid and there are some that have two currently)...

SELECT `tmplvarid`, `contentid`, COUNT(*) a FROM `modx_site_tmplvar_contentvalues`
GROUP BY `tmplvarid`, `contentid`
HAVING a > 1;

This gives me results like this (only first few) showing me which have wrongly got two rows………
tmplvarid       contentid      a (confirm two found with same contentid)      
1141      7566      2
1141      8099      2
1141      8100      2
1141      8101      2
1141      8200      2
1141      8201      2
1141      8202      2
etc..

There are just over 100 of tmplvarid 1141, about the same for 1142 and 1143 that have the two rows - 1141 and 1142 I need to delete higher ids, 1143 I need to delete lower ids - the "id" column isn't shown in my select as I wasn't sure how in the above.

Digging around the web I think I might need some combination of delete below combined with the select above - could just do for one tmplvarid at a time:

DELETE FROM `modx_site_tmplvar_contentvalues`
WHERE ...... (count ? , tmplvarid = 1141...)
ORDER BY id DESC
LIMIT 1

any ideas?
thanks



PS for reference the table structure for modx template variables table is:
--
-- Table structure for table `modx_site_tmplvar_contentvalues`
--

CREATE TABLE `modx_site_tmplvar_contentvalues` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tmplvarid` int(10) NOT NULL DEFAULT '0' COMMENT 'Template Variable id',
  `contentid` int(10) NOT NULL DEFAULT '0' COMMENT 'Site Content Id',
  `value` text,
  PRIMARY KEY (`id`),
  KEY `idx_tmplvarid` (`tmplvarid`),
  KEY `idx_id` (`contentid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Site Template Variables Content Values Link Table' AUTO_INCREMENT=557450 ;

--
SOLUTION
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
OK. It would have been useful to know why it didn't work, but deadlines are deadlines. I did test with some sample data and it successfully deleted all but the first instance (going by lowest ID number) of each duplicate.
Avatar of freshwaterwest

ASKER

I ended up doing this a different way in the end, more manual but relatively easy in phpmyadmin after using the select I posted. I could easily go through and check alternate rows and group delete them which wasn't that big a deal in the end - time contraints decided the route to go too.