1

I'm on SQL Server 2014 and I need to clean a table from duplicates. Problem is: if a duplicate has references in other tables, that references should be updated to reference the chosen non-duplicate entity.

E.g., say a have a Person table:

+------+-------+-------+------------------------+
|  id  | fname | lname | mail                   | 
+------+-------+-------+------------------------+
| 1111 | John  | Smith | john.smith@example.com |
| 2222 | J.    | Smith | john.smith@example.com |
| 3333 | john  | smith | john.smith@example.com |
| 4444 | Smith | John  | john.smith@example.com |
+------+-------+-------+------------------------+

I need to delete 2222, 3333, 4444 and replace references around the database to these with a reference to 1111. After this operation, a unique index on mail will be created.

My approach would be gathering all the distinct mail and building a id => [ ids ] map (e.g. 1111 => [ 2222, 3333, 4444]) and with a scripting language like Perl or PHP update all the tables which may have references to the duplicates and setting the correct id.

Since there are thousands of users and hundreds of tables with relations to them, I wonder if operations like this could be done directly in SQL Server function, with something like:

DELETE FROM [Person] WHERE [id] IN (2222, 3333, 4444) UPDATE REFERENCES WITH 1111
1

I think this will do it
I changed this answer as I may have read the question incorrectly
Look at edit history if you are looking for something different

as for the fk

with CTE as 
(  select id, mail 
        , row_number() over (partition by mail order by id) as rn
   from table 
) 
update fk1  
   set fk1.fkID = cte1.ID 
  from fk as fk1 
  join cte as CTE2 
    on CTE2.ID = fk1.fkID
   and CTE2.rn > 1 
  join cte as CTE1 
    on CTE1.mail = CTE2.mail 
   and CTE1.rn = 1;

run above for addition fk

delete *  
from  
(  select id, mail 
        , row_number() over (partition by mail order by id) as rn
   from table 
) as t2 
where rn > 1;
  • Will this work given id being the primary key? I tried and I get "Cannot update identity column 'id'". – Giovanni Lovato Mar 21 '16 at 20:09
  • It makes not sense (to me) that a fkID would be an identity column. Post the 'fk' table with definition and sample data. – paparazzo Mar 21 '16 at 20:13
1

You can use the ROW_NUMBER() functionality to determine which duplicate key to keep, and which duplicate keys need to be updated to the one you're keeping.

As a test-bed, I setup the following

IF COALESCE(OBJECT_ID('dbo.T2'), 0) <> 0
DROP TABLE dbo.T2;

IF COALESCE(OBJECT_ID('dbo.T1'), 0) <> 0
DROP TABLE dbo.T1;

CREATE TABLE dbo.T1
(
    T1_ID INT NOT NULL
        CONSTRAINT PK_T1
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.T2
(
    T2_ID INT NOT NULL
        CONSTRAINT PK_T2
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , T1_ID INT NOT NULL
        CONSTRAINT FK_T2_T1
        FOREIGN KEY 
        REFERENCES dbo.T1(T1_ID)
    , SomeMoreData VARCHAR(255) NOT NULL
);

Here, I'm inserting duplicate values into "SomeData", with unique values for "T1_ID":

INSERT INTO dbo.T1(T1_ID, SomeData)
SELECT o.object_id,
    o.name
FROM sys.objects o
ORDER BY o.object_id;

INSERT INTO dbo.T1(T1_ID, SomeData)
SELECT o.object_id * -1,
    o.name
FROM sys.objects o
ORDER BY o.object_id;

And here we're generating the same duplicate rows for T2:

INSERT INTO dbo.T2(T1_ID, SomeMoreData)
SELECT o.object_id
    , o.name + '-' + o.type_desc
FROM sys.objects o
ORDER BY o.object_id;

INSERT INTO dbo.T2(T1_ID, SomeMoreData)
SELECT o.object_id * -1
    , o.name + '-' + o.type_desc
FROM sys.objects o
ORDER BY o.object_id;

To provide a mechanism for temporarily storing the mapping of old keys to new keys, I created the following table. This allows multiple child tables to be easily updated, without needing to continuously recalculate the duplicates from the parent table:

IF COALESCE(OBJECT_ID('dbo.DupKeys'), 0) <> 0 
DROP TABLE dbo.DupKeys;

CREATE TABLE dbo.DupKeys 
(   OldKey INT NOT NULL
    , NewKey INT NOT NULL
    , CONSTRAINT CK_DupKeys
        CHECK (OldKey <> NewKey)
);

This populates the DupKeys tables. Essentially, the idea here is to use ROW_NUMBER() partitioned by the column(s) that contain duplicate values (in your case this would be fname, lname, mail):

;WITH MinKeys
AS
(
    SELECT MinKey = T1.T1_ID
        , T1.SomeData
        , rn = ROW_NUMBER() OVER (PARTITION BY T1.SomeData ORDER BY T1.T1_ID)
    FROM dbo.T1
)
INSERT INTO dbo.DupKeys (OldKey, NewKey)
SELECT OldKey = T1.T1_ID
    , NewKey = MinKeys.MinKey
FROM MinKeys
    INNER JOIN dbo.T1 ON MinKeys.SomeData = T1.SomeData
WHERE MinKeys.rn = 1
    AND MinKeys.MinKey <> T1.T1_ID;

This T-SQL will update the duplicate keys based on the mappings in the DupKeys table above:

UPDATE dbo.T2
SET T2.T1_ID = DupKeys.NewKey
FROM dbo.T2
    INNER JOIN dbo.DupKeys ON t2.T1_ID = DupKeys.OldKey
WHERE T2.T1_ID = DupKeys.OldKey;

The above could be used for multiple tables.

This can be used to eventually delete the duplicates from the parent table after all child tables have been updated.

DELETE
FROM dbo.T1
WHERE NOT EXISTS (
    SELECT 1
    FROM dbo.T2
    WHERE T2.T1_ID = T1.T1_ID
    );

Then, a unique index can be added to prove there are no duplicates remaining, and to prevent future duplicates being inserted:

CREATE UNIQUE INDEX IX_T1_SomeData ON dbo.T1(SomeData);

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.