SQL: UPDATE if not exists, else DELETE

not-exists, sql, sql-delete, sql-server, sql-update

Solution

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

Problem

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

Original source