DELETE from TABLE WHERE?

Question

I have a table and I need the correct statement to delete duplicate records. I want to achieve this by deleting rows in the table where multiple column values equal those of other records in the same table. I can do simple delete from [table] where [col] = [val] but I have no idea how to do this. Can you provide a template that I can work from?

Thanks.

and http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Paul Dixon, Jun 17 '13 at 16:02
Please don't use the question title to "tag" your questions, the tagging system is designed for that. — Burhan Khalid, Jun 17 '13 at 16:05
This has been answered before http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-mysql-table — Nick, Jun 17 '13 at 16:05
@BurhanKhalid: OK, I will stop doing this. Thanks for the links. I will look. — uncle-junky, Jun 17 '13 at 16:11

juergen d · Answer 1 · 2013-06-17T16:06:52.860

1

delete from [table] 
where [col] not in 
(
  select * from 
  (
    select min([col])
    from [table]
    group by [col]
    having count(distinct [val]) <> count([val])
  ) x
)

If you select from a table you are deleting from in MySQL then you have to use a subquery to hide that. That is why I used the select * from (...) x

edited Jun 17 '13 at 16:06

answered Jun 17 '13 at 16:01

juergen d

201,996
37
293
362

score 0 · Answer 2 · answered Jun 17 '13 at 16:01

0

Assuming that the original key's id is say 7,

DELETE FROM [table] WHERE [col1] = [val1] AND [col2] = [val2] .... AND [id] != 7;

answered Jun 17 '13 at 16:01

Kevin

6,539
5
44
54

Thanks, though I won't be hard coding the ID in my script as there are many records in my table, so I'd like to remove all records that are like any other and keep (the most recent perhaps? I do have a timestamp of each record) one of them. I'll take a look at @juergen's answer. – uncle-junky Jun 17 '13 at 16:07

DELETE from TABLE WHERE?

2 Answers2