MySql Query Select Then Delete

Question

I have used a mysql query to select my duplicate entries in Database.

select id,DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id ,count(*) from circuit_u
WHERE DATE_FORMAT(cu_date,'%Y-%m-%d') = '2013-01-26'
group by DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id
HAVING count(*) > 1;

Output:

16347   2013-01-26 01:00:00 0   2
16372   2013-01-26 01:00:00 1   2
16397   2013-01-26 01:00:00 100 2
16422   2013-01-26 01:00:00 101 2
16447   2013-01-26 01:00:00 121 2
16472   2013-01-26 01:00:00 211 2
16497   2013-01-26 01:00:00 221 2
16522   2013-01-26 01:00:00 311 2

Now I want to add another command to delete them.

I have tried with so many way but every time i failed.

Please help.

Answer 1

You can't access and delete rows from the same table in the same SQL query, no matter how you try, trust me. You'll need at least two queries.

I've done this before with three: "a query to create a temporary memory table for the IDs; another one to populate it with the row IDs you want to delete (or keep..); a last query which relates the original and the temporary table to delete rows from the first one."

(Note: If you use temporary tables, you'll have to execute the three queries in the same connection.)

Answer 2

you can try this

   DELETE from circuit_u WHERE id IN ( select * from (
   select id from circuit_u
   WHERE DATE_FORMAT(cu_date,'%Y-%m-%d') = '2013-01-26'
   group by DATE_FORMAT(cu_date,'%Y-%m-%d %T'),trunk_id
   HAVING count(*) > 1 ) t
   )