Flagging duplicates

Question

I have a huge table with a couple of 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

Answer 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