Solved

delete higher id rows where column has two same

Posted on 2015-02-16
4
157 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: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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mySql Syntax 7 53
html input clean up 3 55
when to use sequences in mysql 4 29
PHP insert data in to database... this is not letting me in insert data...please help! 7 38
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

772 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