Delete duplicate mysql rows without primary key

Question

Hi, I have a mysql table without a primary key, and I need to delete duplicate rows. How can i do this?

user_id category_id 1 2 1 3 1 4 1 2 2 2 2 3 2 2 

Answer 1

  CREATE TABLE temp SELECT DISTINCT * FROM tablename; ALTER TABLE tablename RENAME junk; ALTER TABLE temp RENAME tablename; 

Answer 2

Since you cannot distinguish 2 identical lines, you cannot delete only one of them. The way you should think about it is as follows:

 insert into new_better_table select user_id, category_id from old_table group by user_id, category_id 

Answer 3

You can use the dirty flag with a default value of 1 and copy only individual entries with the flag set to 0 , and then simply delete all the dirty entries. This way you do not need another table.

Assuming you already created a dirty flag with a default value of 1 :

 insert into mytable select fld1,fld2,fldN,0 as dirty from mytable group by duplicate_field 

Then you can simply delete the dirty entries:

 delete from mytable where dirty = 1 

Remember to remove the dirty flag. you did it