0

I have a huge table with a couple of duplicates:

Duplicates

I want to add a new column that shows some value when duplicates occur, something like:

case when fist.firstname = last.firstname then 'yes' else 'no' end duplicate
  • Is this SQL Server, and if so what version? – Pixelated Mar 18 '16 at 12:04
  • 1
    Which DBMS are you using? – a_horse_with_no_name Mar 18 '16 at 12:04
  • it is SQL 2012 and i am using MS SQL Managemet Studio – itsAftab Mar 18 '16 at 14:33
1

The below can be executed post event via an SP. Alternatively you might want to add the below logic into an after trigger per row inserted. This is dependant on system performance requirements.

Sample data

IF OBJECT_ID('tempdb..#Contact', 'U') IS NOT NULL
        DROP TABLE #Contact ;

    CREATE 
    TABLE   #Contact
            (
            person_id INT,
            FirstName VARCHAR(50) NULL,
            Duplicate TINYINT NULL
            );

    INSERT
    INTO    #Contact
            (
            person_id
            ,FirstName
            )

    VALUES  (111,'A'),       
            (111,'A'),
            (111,'A'),       
            (112,'B'),
            (112,'B'),       
            (112,'B'),
            (113,'C')

Query to update a third column with duplicate status

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY c.person_id ORDER BY c.person_id DESC) AS RowNumber
               ,c.Duplicate
               ,c.person_id
        FROM    #Contact c
        )

UPDATE  dc
SET     dc.Duplicate = 1
FROM    CTERowNumber dc     
WHERE EXISTS 
        (
        SELECT  *
        FROM    CTERowNumber dc1
        WHERE   dc.person_id = dc1.person_id
        AND     dc1.RowNumber > 1
        )
AND dc.Duplicate IS NULL

Query to update only the second, third etc duplicates

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY c.person_id ORDER BY c.person_id DESC) AS RowNumber
               ,c.Duplicate
               ,c.person_id
        FROM    #Contact c
        )

UPDATE  dc
SET     dc.Duplicate = 1
FROM    CTERowNumber dc  
WHERE   dc.RowNumber > 1
AND     dc.Duplicate IS NULL

Query to delete duplicates. Update ORDER BY c.person_id DESC part of query to identify which duplicate rows to delete depending on business logic

;WITH   CTERowNumber
AS      (
        SELECT  ROW_NUMBER() OVER (PARTITION BY c.person_id ORDER BY c.person_id DESC) AS RowNumber
               ,c.Duplicate
               ,c.person_id
        FROM    #Contact c
        )

DELETE  dc
FROM    CTERowNumber dc     
WHERE   dc.RowNumber > 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.