Delete duplicated rows from database

Question

I need to delete duplicated rows from database. Can i do it with simple sql query? If not, please, show me some quick algorythm to do it.

Example:

id| field_one | field_two |
1 | 0000000   | 11111111  |
2 | 2222222   | 33333333  |
3 | 2222222   | 33333333  |
4 | 4444444   | 55555555  |

I need to delete row with id 2 (or 3, no matter, they are equal, but not both). Thanks for any help

Answer 1

delete from the_table where id in
   (select max(id) from the_table
      group by field_one, field_two
      having count(*) > 1)

As pointed out in the comments, this will not work if a row appears three times. You can run this (heavy) query repeatedly until it stops deleting stuff, or wait for a better answer...

Answer 2

Maybe this will help you http://support.microsoft.com/kb/139444

Answer 3

First select all the distinct rows and then delete the other ones:

DELETE FROM MyTable 
WHERE id NOT IN
      (
        SELECT MAX(id) FROM MyTable
        GROUP BY field_one, field_two
      )

Answer 4

Thilo's answer is a useful one, it just makes what you want. Anyway if you have many lines it could take up much time as the algorithm has a square complexity. If I were the person who asked, I would choose Thilo's answer as best answer, anyway I just want to give you another option: if you have many lines then another possibility is:

create a new table, set up a UNIQUE INDEX for the column combination: (field_one, field_two) and copy the content of the first table into the new one. Then you delete the old one and rename the new one to the old table name.

That's all.

Answer 5

set rowcount 1 
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
while @@rowcount > 0 
delete userTbl1 from userTbl1 a1 where (select count(UName) from userTbl1 a2 where a2.UName =a1.UName)>1
set rowcount 0