CODEWITHSUNDEEP
sqlsql-serverduplicatessql-delete
Emil Smęt
Emil Smęt

Reputation: 909

SQL delete almost identical rows

I have a table that have 5 columns, and instead of update, I've done insert of all rows(stupid mistake). How to get rid of duplicated records. They are identical except of the id. I can't remove all records, but I want do delete half of them.

ex. table:

+-----+-------+--------+-------+
| id  | name  | name2  | user  |
+-----+-------+--------+-------+
|   1 | nameA | name2A | u1    |
|  12 | nameA | name2A | u1    |
|   2 | nameB | name2B | u2    |
| 192 | nameB | name2B | u2    |
+-----+-------+--------+-------+

How to do this? I'm using Microsoft Sql Server.

Upvotes: 0

Views: 168

Answers (3)

gzaxx
gzaxx

Reputation: 17600

This is more specific query than @TechDo as I find duplicates where name, name2 and user are identical not only name.

with duplicates as
(
    select t.id, ROW_NUMBER() over (partition by t.name, t.name2, t.[user] order by t.id) as RowNumber
    from YourTable t
)

delete duplicates 
where RowNumber > 1

SQLFiddle demo to try it yourself: DEMO

Upvotes: 0

Alec.
Alec.

Reputation: 5535

Try the following.

    DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY Name, Name2, User)

That is untested so may need adapting. The following video will provide you with some more information about this query.

Video

Upvotes: 2

TechDo
TechDo

Reputation: 18659

Please try:

with c as
(
    select 
        *, row_number() over(partition by name, name2, [user] order by id) as n
    from YourTable
)
delete from c
where n > 1;

Upvotes: -1

Related Questions