I have a table, called SITES, that has three columns, lets say it looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/

I also have a related table called Logs that looks like this:

ID  SiteID   LogData
1   1        --data--
2   1        --more--
3   2        --other--

The SITES table gets populated both by users of the system and also occasionally some batch processes. From the batch process, I don't always have the 'name' available, so a record is created that looks like this in SITES (when searching by Path yields no result), because the batch process is mostly interested in creating data in the Log table.

ID  Name        Path
99              http://arstechnica.com/

The problem situation arises like this:

1) User creates new SITE, but doesn't enter the Path (doesn't know it, or in some cases it doesn't yet exist) so the SITES table looks like this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo

2) The batch process comes along and needs to add a log for http://yahoo.com, searches SITES by Path and does not find it, so it makes its own and the result is this:

ID  Name        Path
1   Google      http://www.google.com/
2   Microsoft   http://www.microsoft.com/
3   Yahoo
4               http://yahoo.com

My question is, how can I merge Record 3 and record 4 while preserving the referential integrity of both records? Lets say for the sake of argument, that each record has several related records in both the Log table, as well as some other tables.

I understand that it will be a manual process of identifying records that need to be updated, so let any solution assume that I have audited the list and found all of the "duplicate" records.

  • how did you define your foreign keys?
    – swasheck
    Commented Oct 25, 2012 at 19:34
  • @swasheck Using SSMS Relationship builder.
    – Nate
    Commented Oct 25, 2012 at 19:39
  • 1. update the site record you want to keep so that it has a name and path 2. change the site id in the "bad" log records to point to the good site record 3. now delete the bad site record since there are no more linking records in the log table
    – Jimbo
    Commented Oct 25, 2012 at 19:39

2 Answers 2


This is a very straight-forward data cleanup task. Your solution is good (I'd recommend using a transaction construct as below, just to be safe) -- I've taken the same approach except I used set-based operations so it can be scaled up and automated once you come up with a better duplicate detection algorithm than the rudimentary one I provided.

/* Test setup */
CREATE TABLE [dbo].[Sites]
    Name nvarchar(50) NULL,
    Path nvarchar(100) NULL

CREATE TABLE [dbo].[Logs]
    SiteId int NOT NULL FOREIGN KEY REFERENCES [dbo].[Sites](Id),
    LogData nvarchar(MAX) NULL

INSERT INTO [dbo].[Sites](Id, Name, Path)
        (1, N'Google', N'http://www.google.com'),
        (2, N'Microsoft', N'http://www.microsoft.com'),
        (3, N'Yahoo', NULL),
        (4, NULL, N'http://www.yahoo.com');

INSERT INTO [dbo].[Logs](Id, SiteId)
    VALUES (1, 1), (2, 1), (3, 2), (4, 3), (5, 3), (6, 4);

/* Identify potential duplicates (note: very rough) */
    s1.Id AS SourceId, s1.Name AS SourceName, s1.Path AS SourcePath,
    s2.Id AS DuplId, s2.Name AS DuplName, s2.Path AS DuplPath
    FROM [dbo].[Sites] s1
    INNER JOIN [dbo].[Sites] s2 ON
        (s2.Path LIKE (N'%' + s1.Name + N'%')) AND
        (s2.Id > s1.Id);

/* Merge duplicates */
DECLARE @duplicates table
    SourceId int NOT NULL,
    TargetId int NOT NULL,
    PRIMARY KEY (SourceId, TargetId),
    CHECK (SourceId != TargetId)

INSERT INTO @duplicates(SourceId, TargetId)
    VALUES ; /* Edit me! */



    UPDATE l
        SET SiteId = d.TargetId
        FROM @duplicates d
        INNER JOIN [dbo].[Logs] l ON l.SiteId = d.SourceId;

    UPDATE st
            Name = COALESCE(ss.Name, st.Name),
            Path = COALESCE(ss.Path, st.Path)
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] ss ON ss.Id = d.SourceId
        INNER JOIN [dbo].[Sites] st ON st.Id = d.TargetId;

    DELETE s
        FROM @duplicates d
        INNER JOIN [dbo].[Sites] s ON s.Id = d.SourceId;


/* Validate the site attributes were merged correctly */
    FROM @duplicates d
    INNER JOIN [dbo].[Sites] s ON s.Id = d.TargetId;
  • Nice answer, simple yet right. Commented Oct 29, 2015 at 18:11

So, taking Jimbo's advise, I wrote a quick query to do this. If anyone has a better solution, or an improvement on the below query, please make the edit or post a new answer.

DECLARE @keeperID int;
DECLARE @gonerID int;
DECLARE @newPath nvarchar(max);

SET @keeperID = 3;
SET @gonerID = 4;
SET @newPath = (SELECT Path FROM [SITES] WHERE [ID] = @gonerID);

SET [Path] = @newPath 
WHERE [ID] = @keeperID;

SET [SiteID] = @keeperID
WHERE [SiteID] = @gonerID;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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