Solved

delete higher id rows where column has two same

Posted on 2015-02-16
4
163 Views
Last Modified: 2015-02-22
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 ;

--
0
Comment
Question by:freshwaterwest
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:Simon
Simon earned 250 total points
ID: 40613003
I think this will do it for you:

delete from `modx_site_tmplvar_contentvalues` 
where id in (select * from 
             (select  t2.ID 
              FROM `modx_site_tmplvar_contentvalues` t1 
              inner join `modx_site_tmplvar_contentvalues` t2 
              on t1.tmplvarid=t2.tmplvarid and t2.id >t1.ID)as d1)

Open in new window


In MySQL it seems necessary to nest the subquery to avoid MySQL error #1093 - Can't specify target table for update in FROM clause.

If you run it in phpmyadmin, I'd suggest you simulate the query first, to check that it identifies the expected number of rows.
0
 

Accepted Solution

by:
freshwaterwest earned 0 total points
ID: 40613774
I couldn't get this to work unfortunately, so due to deadline I ended up stripping out the ids from my earlier select and using it to list them all and go through deleting manually in phpmyadmin overnight which wasn't such a big deal after all. Not sure where to go from here - it would be useful to know for future but as I say it didn't work. I maybe didn't clarify that the decider is the id (as I could tell the earlier / later additions by unique id numbers so decide which to get rid of), the contentid shows the duplicates and tmplvarid to decide which template variable in modx.

Here's how I listed them in order to delete manually:
(this is the one with the least)

SELECT id, contentid, tmplvarid FROM `modx_site_tmplvar_contentvalues` WHERE tmplvarid = 1142 AND contentid IN (
8888, 8930, 9090, 9091, 9902, 9903, 9904, 9906, 9907, 9908, 9909, 14953, 14954, 18165, 18166, 18167, 18168, 18169, 18170, 18175, 19334, 19335
)
ORDER BY contentid, id

I did try a few combinations of sub-queries to select only the higher ids - I tried max but ended up with one row result.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40613798
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.
0
 

Author Closing Comment

by:freshwaterwest
ID: 40624038
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.
0

Featured Post

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question