Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
To be clear, in your example it's the table at the other end of FK_row2 which is being merged, right? –  Jon of All Trades Mar 13 '12 at 23:14
    
correct. the other tables rows merge so that merge must also be reflected in the table with FK_row2 –  corymathews Mar 14 '12 at 12:49

2 Answers 2

up vote 1 down vote accepted

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
share|improve this answer

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

share|improve this answer
    
The logic for that seemed more complex then just deleting them all and re adding them all. –  corymathews Mar 14 '12 at 12:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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