SQL: UPDATE if not exists, else DELETE

Question

I'm currently trying to write a script that will change a user's role to another, without creating duplicates in a SQL Server database.

For example:

User_ID      Role_ID
---------------------
A            X
A            Z
B            Y
C            X
C            Y
D            Y

Users may have more than one role.

I want to change it so that all users in role Y are now members of the role X, and the role Y will no longer exist as such:

User_ID      Role_ID
---------------------
A            X
A            Z
B            X
C            X
D            X

By updating all Y roles to X, this will potentially create duplicate values; therefore I need to only update if the new value doesn't already exist, else just delete this value

Answer 1

Try this (SQL DEMO)

--Delete records with X if same user has Y 
delete t1
from userRoles t1 join (
    select * from userRoles t2 where t2.role_id = 'y') t3
    on t1.user_id = t3.user_Id
where t1.role_id = 'x'

--Update all Y records to X
update userRoles set role_id = 'X'
where role_id = 'y'

select * from userRoles
--RESULTS
USER_ID ROLE_ID
A         X
A         Z
B         X
C         X
D         X

Answer 2

You can do it in a single MERGE statement (it offers optimized locking):

MERGE   tbl2
USING   (
                -- Create the desired data (together with duplicates)
        SELECT  User_ID, 
                CASE WHEN Role_ID = 'Y' THEN 'X' ELSE Role_ID END Role_ID
        FROM    tbl2
) new ON tbl2.User_ID = new.User_ID AND tbl2.Role_ID = new.Role_ID
WHEN NOT MATCHED THEN
                -- Insert the updated data
        INSERT(User_ID, Role_ID) VALUES(User_ID, Role_ID)
WHEN NOT MATCHED BY SOURCE THEN
                -- Filter out the 'X'-s
        DELETE;

Here is an SQL Fiddle