Geek Answers Handbook

SQL: UPDATE if it does not exist, otherwise DELETE

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

For instance:

User_ID Role_ID --------------------- AX AZ BY CX CY DY

Users can have more than one role.

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

 User_ID Role_ID --------------------- AX AZ BX CX DX

Upgrading all Y roles to X, this potentially creates duplicate values; so I only need to update if the new value does not exist yet, otherwise just delete this value

+4
source share
4 answers

Try ( 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 AX AZ BX CX DX
+2
source

You can do this in one MERGE (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 the SQL Fiddle

+2
source

This can be done using MERGE :

 DECLARE @T TABLE (User_ID CHAR(1), Role_ID CHAR(1)); INSERT @T (User_ID, Role_ID) VALUES ('A', 'X'), ('A', 'Z'), ('B', 'Y'), ('C', 'X'), ('C', 'Y'), ('D', 'Y'); SELECT * FROM @T; WITH Y AS ( SELECT *, [d] = COUNT(*) OVER(PARTITION BY User_ID) FROM @T WHERE Role_ID IN ('Y', 'X') ), X AS ( SELECT * FROM @T WHERE Role_ID = 'X' ) MERGE INTO Y USING X ON X.User_ID = Y.User_ID WHEN MATCHED AND Y.Role_ID = 'Y' AND d > 1 THEN DELETE WHEN NOT MATCHED BY SOURCE THEN UPDATE SET Role_ID = 'X'; SELECT * FROM @T;
+1
source

First you need to clear the table:

 DECLARE @originalRole char(1) = 'Y', @newRole char(1) = 'X' DELETE [r1] FROM [Roles] [r1] WHERE [r1].[Role_ID] = @originalRole AND EXISTS(SELECT * FROM [Roles] [r2] WHERE [r2].[User_ID] = [r1].[User_ID] AND [r2].[Role_ID] = @newRole)

And after that, update the remaining lines, if any:

 UPDATE [Roles] SET [Role_ID] = @newRole WHERE [Role_ID] = @originalRole

And here is the screenshot .

0
source

More articles:


All Articles