Home Delete records based on another query in mysql
Reply: 2

Delete records based on another query in mysql

nmr
1#
nmr Published in 2018-01-13 00:13:14Z

I have a query in MySQL based on which I am finding duplicate records of some columns.

select max(id), count(*) as cnt 
from table group by start_id, end_id, mysqltable 
having cnt>1;

This above query gives me the max(id) and the count of number of records that have start_id,end_id,mysqltable column values same.

I want to delete all the records that match the max(id) column of the above query

How can I do that?

I have tried like below

delete from table 
where (select max(id), count(*) as cnt 
from table group by start_id,end_id,mysqltable 
having cnt>1)

But Unable to delete records

Anuruddha
2#
Anuruddha Reply to 2018-01-13 00:56:13Z

You can remove duplicate records using JOIN.

DELETE t1 FROM table t1
INNER JOIN
  table t2
WHERE 
t1.id > t2.id AND t1.start_id = t2.start_id AND  t1.end_id = t2.end_id AND t1.mysqltable = t2.mysqltable;  

This query keeps the lowest id and remove the highest.

Patrick Arguello
3#
Patrick Arguello Reply to 2018-01-13 01:20:54Z

I think so this command should work:

  delete from table 
    where id in  
    ( select max(id) from table 
         group by start_id, end_id, mysqltable 
         having count(*) > 1 
    );
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.343907 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO