Deleting duplicates based on multiple columns

Question

I have listed duplicates using the following:

select s.MessageId, t.* 
from Message s
join (
    select ToUserId, FromUserId, count(*) as qty
    from Message
    group by ToUserId, FromUserId
    having count(*) > 1
) t on s.ToUserId = t.ToUserId and s.FromUserId = t.FromUserId

Now, how do I delete all but one of the Messages (I'm trying to remove duplicates so I can apply a unique index on FromUserId and ToUserId).

Possible duplicate of [Removing duplicate rows (based on values from multiple columns) from SQL table](http://stackoverflow.com/questions/30243945/removing-duplicate-rows-based-on-values-from-multiple-columns-from-sql-table) — FuzzyTree, Jan 03 '17 at 06:49
@FuzzyTree why would you vote to close if there are already useful answers? — SB2055, Jan 03 '17 at 16:52
Because it's still a duplicate question and duplicate questions clutter the website without providing any value to future visitors — FuzzyTree, Jan 03 '17 at 17:29
@FuzzyTree look at the link you provided. Both the question and answer are muddled. Duplicate intentions doesn't equate to duplicate results - we have a more accessible resource here now. — SB2055, Jan 03 '17 at 22:28

score 19 · Accepted Answer · edited Oct 17 '18 at 07:38

19

Use a cte and assign row numbers so that all but one for duplicate pairs can be deleted.

with rownums as 
(select m.*, 
 row_number() over(partition by ToUserId, FromUserId order by ToUserId, FromUserId) as rnum
 from Message m)
delete r
from rownums r
where rnum > 1

edited Oct 17 '18 at 07:38

hawbsl

15,313
25
73
114

answered Jan 03 '17 at 02:17

Vamsi Prabhala

48,685
4
36
58

We can also use DENSE_RANK() instead of ROW_NUMBER. – Peter Oct 23 '18 at 10:21

score 0 · Answer 2 · answered Jan 03 '17 at 03:10

Make sample data

    DECLARE @Message TABLE(ID INT ,ToUserId varchar(100),FromUserId varchar(100))
    INSERT INTO @Message(ID,ToUserId, FromUserId )
    VALUES  ( 1,'abc',  'def'  ), ( 2,'abc',  'def'  ), ( 3,'abc',  'def'  ), ( 4,'qaz',  'xsw'  )

--Do delete data

    DELETE m FROM @Message AS m 
    INNER JOIN (
         SELECT *,row_number()OVER(PARTITION BY ToUserId,FromUserId ORDER BY ID ) AS rn FROM @Message AS m
    ) t ON t.ID=m.ID
    WHERE t.rn>1

    SELECT * FROM @Message

----------- ---------- ----------
1           abc        def
4           qaz        xsw

If there is no column to indicate specify line as ID, you can try to use address of line (for example %%lockres%%)

    DELETE m FROM @Message AS m 
    INNER JOIN (
        SELECT *,row_number()OVER(PARTITION BY ToUserId,FromUserId ORDER BY %%lockres%% ) AS rn FROM @Message AS m
    ) t ON t.ID=m.ID
    WHERE t.rn>1

    SELECT *, %%lockres%% FROM @Message

Deleting duplicates based on multiple columns

2 Answers2

Linked