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.

if you have tried so many why dont you post them ? so we can correct you not give you the coocked answer — echo_Me
– echo_Me, Commented Aug 4, 2013 at 9:35
Do you want to delete only the duplicates or every entry that has a duplicate? — Krisztián Balla
– Krisztián Balla, Commented Aug 4, 2013 at 9:48
every entry that has a duplicate. i mean whatever my above query returns will be deleted. because i am able to find the duplicate data only — iamlancer
– iamlancer, Commented Aug 4, 2013 at 9:49
The query you posted should not work, because you specify different columns in your projection (select) than in your grouping (group by). Does that query really work? — Krisztián Balla
– Krisztián Balla, Commented Aug 4, 2013 at 9:54
Your query is still wrong. You group by columns and then specify different columns in your select. How should you be able to get the ID (as first column), after you have already grouped your data by date and another id? This does not work! — Krisztián Balla
– Krisztián Balla, Commented Aug 4, 2013 at 10:08

echo_Me · Accepted Answer · 2013-08-04 10:22:04Z

3

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
   )

edited Aug 4, 2013 at 10:22

answered Aug 4, 2013 at 9:58

echo_Me

37.2k5 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

iamlancer Over a year ago

sorry man no use. error 1241 operand should contain one column

Akash Over a year ago

This would delete all duplicate entries along, dosen't the OP wants to keep at-least 1 of the rows that contains duplicated value(s)

juanra · Accepted Answer · 2013-08-04 11:38:15Z

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.)

answered Aug 4, 2013 at 11:38

juanra

1,66219 silver badges18 bronze badges

Collectives™ on Stack Overflow

MySql Query Select Then Delete

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related