Solved

delete higher id rows where column has two same

Posted on 2015-02-16
4
150 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
  • 2
  • 2
4 Comments
 
LVL 18

Assisted Solution

by:SimonAdept
SimonAdept 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:SimonAdept
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now