Remove 1 Duplicate row from SQL Table if two column data matches

Question

I have three columns in my table

KeyName, Number, Data

The data consist of lot of duplicate entries. I need to delete the 1 row from each of the duplicate entries in the table if the "KeyName" and "Data" are repeated. How shall I achieve this is SQL Query .

Mine is SQL server. I have only few entries which are more than two duplicates. Which i thought of deleting manually. — Peekay, Apr 15 '16 at 16:58

score 0 · Answer 1 · answered Apr 15 '16 at 17:01

0

You can keep one row for each pair by using row_number():

with todelete as (
      select t.*,
             row_number() over (partition by keyname, data order by (select NULL)) as seqnum
      from t
     )
delete
from todelete
where seqnum > 1;

As with any such operation, you should check first before deleting. This is easy:

with todelete as (
      select t.*,
             row_number() over (partition by keyname, data order by (select NULL)) as seqnum
      from t
     )
select *
from todelete
where seqnum > 1;

answered Apr 15 '16 at 17:01

Gordon Linoff

1,242,037
58
646
786

Just received correct answer as yours. Sorry stackoverflow doesnt allow two answers to be marked as correct .Thank you for your response – Peekay Apr 15 '16 at 17:09
@arun . . . The decision of which answer to accept is wholly up to the OP. – Gordon Linoff Apr 16 '16 at 02:26

score 0 · Accepted Answer · answered Apr 15 '16 at 17:02

This should do it

WITH CTE
    AS (SELECT KeyName
            , Number
            , Data
            , [rn] = ROW_NUMBER() OVER(PARTITION BY KeyName
                                          , Data ORDER BY KeyName
                                                     , Data)
        FROM   yourtable)
     /* UNCOMMENT BELOW TO DELETE */
     --DELETE
    --FROM   CTE
    --WHERE  rn > 1;

    SELECT *
    FROM   CTE
    WHERE  rn > 1;

Remove 1 Duplicate row from SQL Table if two column data matches

2 Answers2