freshwaterwest
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_content values`
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_content values`
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_content values`
--
CREATE TABLE `modx_site_tmplvar_content values` (
`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 ;
--
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_content
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_content
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_content
--
CREATE TABLE `modx_site_tmplvar_content
`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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.