Merging Results while avoiding Key Constraint Error?

Question

I have a table with a primary key which is two integer fields, each a foreign key to a different table. (Multi to Multi table)

In one of the foreign tables two rows are merged and this table is updated to match the changes which can result in two rows being duplicates and cause the query to fail.

There has to be a better way to do the following: (which does work)

SELECT row1, row2 
FROM table 
WHERE row2 = @ID OR FK_Gmb_row2 = @ID2 GROUP BY row1, row2;

DELETE FROM table WHERE row1 = @ID OR row2 = @ID2;

-- process select in code and loop over results::
INSERT INTO table (row1, row2) VALUES (@val1, @val2)

So if FK_row had the values 2 and 3 merged Table 1 would go from

FK_row1 , FK_row2
-----------------
1       , 1
1       , 2
1       , 3
2       , 3

To

FK_row1 , FK_row2
-----------------
1       , 1
1       , 3
2       , 3

I feel like I am using this solution because I do not properly understand something about the database system.

Answer 1

If I'm following you correctly, you can do what you need in a single MERGE statement, if you want to be cute about it. I suspect you'd get better performance with separate small INSERT and DELETE operations.

CREATE TABLE #ManyMany
(
FK1 INT NOT NULL,
FK2 INT NOT NULL,
PRIMARY KEY (FK1, FK2),
UNIQUE (FK2, FK1)
)
INSERT INTO #ManyMany (FK1, FK2) VALUES (1, 1), (1, 2), (1, 3), (2, 3)

CREATE TABLE #Merges
(
OldPK   INT NOT NULL PRIMARY KEY,
NewPK   INT NOT NULL
)
INSERT INTO #Merges (OldPK, NewPK) VALUES (2, 3)

SELECT * FROM #ManyMany
MERGE INTO #ManyMany AS Existing
    USING
        (
        SELECT
            MM.FK1, COALESCE(M.NewPK, MM.FK2) AS FK2
        FROM
            #ManyMany AS MM
            LEFT JOIN #Merges AS M ON MM.FK2 = M.OldPK
        WHERE
            NOT EXISTS (SELECT * FROM #ManyMany AS Existing WHERE MM.FK1 = Existing.FK1 AND M.NewPK = Existing.FK2)
        ) AS New ON Existing.FK1 = New.FK1 AND Existing.FK2 = New.FK2
    WHEN MATCHED AND Existing.FK2 <> New.FK2 THEN UPDATE SET FK2 = New.FK2
    WHEN NOT MATCHED THEN INSERT (FK1, FK2) VALUES (FK1, FK2)
    WHEN NOT MATCHED BY SOURCE THEN DELETE;
SELECT * FROM #ManyMany

Answer 2

Usually we just delete the record that no longer needs to exist. You are deleting both records and reinserting the keeper record.