CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
EvilDr
EvilDr

Reputation: 9610

Merge Upsert without violating unique constraint

I have the following table that links users with skills:

DECLARE @tbl_UsersSkills TABLE (
    userId int NOT NULL,
    skillId int NOT NULL, 
    UNIQUE NONCLUSTERED (userId, skillId)
);

INSERT INTO @tbl_UsersSkills 
    (userId, skillID) 
VALUES 
    (100, 1),(100,2),(100,3),(100,4),(200,1),(200,3),(200,4);

This provides the following result set when selecting all rows:

userId      skillId
----------- -----------
100         1
100         2
100         3
100         4
200         1
200         3
200         4

It has been decided that skill 1 is actually the same as skill 2, so we need to combine 1 & 2 together, leaving only 2.

I figured I could grab the skill 1 users, then delete them and re-insert them, but it seems like a fudge, and will increase the identity value (on the real table), and thought MERGE was a more glamorous option.

I need to get to this result set:

userId      skillId
----------- -----------
100         1 <-- removed because (100,2) exists already
100         2
100         3
100         4
200         1 <-- updated to be (200, 2)
200         3
200         4

Here's my attempt, but I get an error that the MERGE statement is modifying the same row multiple times.

Msg 8672, Level 16, State 1, Line 11 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

Can anyone perfect it?

WITH cte AS (SELECT userId, skillId FROM @tbl_UsersSkills WHERE (skillId IN (@s1, @s2)))
MERGE
    @tbl_UsersSkills tgt
USING 
    cte src
ON 
    (tgt.skillId = src.skillId)
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (skillId) VALUES (@s2)   
WHEN MATCHED THEN UPDATE SET
    tgt.skillId = @s2;

Upvotes: 2

Views: 789

Answers (2)

Stephan
Stephan

Reputation: 6018

My guess is this is probably not a common occurence. So if it works, it works. I think instead of using MERGE, you can just update the UserId's with only skill 1(not skill 2) to skill 2. Then delete any rows with skill 1.

DECLARE @tbl_UsersSkills TABLE (
    userId int NOT NULL,
    skillId int NOT NULL, 
    UNIQUE NONCLUSTERED (userId, skillId)
);

INSERT INTO @tbl_UsersSkills 
    (userId, skillID) 
VALUES 
    (100, 1),(100,2),(100,3),(100,4),(200,1),(200,3),(200,4);

UPDATE @tbl_UsersSkills
SET skillID = 2
WHERE skillID = 1
AND userId NOT IN (SELECT DISTINCT userID FROM @tbl_UsersSkills WHERE skillId = 2)

DELETE 
FROM @tbl_UsersSkills
WHERE skillId = 1

SELECT *
FROM @tbl_UsersSkills

Results:

userId      skillId
----------- -----------
100         2
100         3
100         4
200         2
200         3
200         4

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7763

Try this two statement approach:

DELETE @tbl_UsersSkills
FROM @tbl_UsersSkills u1
WHERE u1.skillId = 1 and EXISTS 
    (SELECT * FROM @tbl_UsersSkills u2 WHERE u2.userId = u1.userId AND u2.skillId = 2)

UPDATE @tbl_UsersSkills
    SET skillId = 2
WHERE skillId = 1

Upvotes: 1

Related Questions