Removing duplicate rows from a database

Question

I need to remove duplicate rows from the database. Can I do this with a simple sql query? If not, please show me some quick algorithm to do this.

Example:

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

I need to delete a line with id 2 (or 3, regardless of whether 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 stated in the comments, this will not work if the line appears three times. You can re-run this (heavy) request until it stops deleting the material or waiting for a better answer ...

Answer 2

First select all the individual rows and then delete the rest:

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

Answer 3

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

Answer 4

Thilo , , . , , , . , , , , : , :

, UNIQUE INDEX : (field_one, field_two) . .

.