CODEWITHSUNDEEP
sqldatabase
Scorpil
Scorpil

Reputation: 1526

Delete duplicated rows from database

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

Upvotes: 3

Views: 5009

Answers (4)

AsifQadri
AsifQadri

Reputation: 2388

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

Upvotes: 2

Ervin
Ervin

Reputation: 2442

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.

Upvotes: 1

CristiC
CristiC

Reputation: 22698

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
      )

Upvotes: 3

Thilo
Thilo

Reputation: 262504

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

Upvotes: 5

Related Questions