Delete duplicates, keeping one of them, grouping by

user_7609649 0
Viewed 375

I need to delete all the duplicates, all but one, for each of the table ids. Like the following. I need to delete all the duplicates on valueid for 01,02,03...

Original:

id      | valueid   |   data
____________________________
01      | 1001      |   datadata1       
01      | 1002      |   datadata2
01      | 1001      |   datadata1
02      | 1323      |   datamoredata123
02      | 1323      |   datamoredata123
03      | 22123     |   evenmoredata
03      | 24444     |   andalsomore

Should end like: 

id      | valueid   |   data
____________________________
01      | 1001      |   datadata1       
01      | 1002      |   datadata2
02      | 1323      |   datamoredata123
03      | 22123     |   evenmoredata
03      | 24444     |   andalsomore

Was trying to do it with something like this, but I don´t get how can I group that delete on the id

WITH CTE AS(
   SELECT valueid,
       RN = ROW_NUMBER()OVER(PARTITION BY valueid ORDER BY valueid)
   FROM tblvalues
)
DELETE FROM CTE WHERE RN > 1

Any suggestions?

Thanks in advance

sql-server sql
3 Answers

You need to add id column to the PARTITION:

WITH CTE AS(
   SELECT valueid,
       RN = ROW_NUMBER()OVER( PARTITION BY id, valueid ORDER BY data)
   FROM tblvalues
)
DELETE FROM CTE WHERE RN > 1

This way you delete duplicate valueid values separately for each id. Column data determines which duplicates are deleted.

user_21497180

You are pretty close. You need to change the partition by clause. You want one row per id/valueid pair, so these should both be in the partitioning clause:

WITH todelete AS (
   SELECT valueid,
       RN = ROW_NUMBER() OVER (PARTITION BY id, valueid ORDER BY data)
   FROM tblvalues
)
DELETE FROM todelete WHERE RN > 1;
user_11440350

A very simple way to do this is to add the UNIQUE index in column (valueid). When you write an ALTER statement, specify the IGNORE keyword.

ALTER IGNORE TABLE tblvalues
ADD UNIQUE INDEX idx_name (valueid);

This will remove all duplicate rows. As an added advantage, future INSERTs that are duplicates will be erroneous. As always, you can take a backup before running something like this.

user_74869030
Related