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

Why not just perform the update and get rid of the duplicates afterwards? If that's not an option: Which RDBMS are you using? — Frank Schmitt, Jan 25 '13 at 11:54
Can you show the expected results based on your sample data above? =) — bonCodigo, Jan 25 '13 at 12:09

Kaf · Accepted Answer · 2013-01-25T12:14:16.297

3

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

edited Jan 25 '13 at 12:14

answered Jan 25 '13 at 12:08

Kaf

33,101
7
58
78

Does the MERGE statement not handle this? Or even a correlated subquery? – ta.speot.is Jan 25 '13 at 12:12
@ta.speot.is: This is a one off query and does the job. I'm not sure about Merge or subqueries. Do you have an example? – Kaf Jan 25 '13 at 12:15
1

Easy to understand and does the job well. Thanks! – Viprus Jan 25 '13 at 12:46

Ivan Golović · Answer 2 · 2013-01-25T20:00:00.187

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

edited Jan 25 '13 at 20:00

answered Jan 25 '13 at 12:31

Ivan Golović

8,732
3
25
31

The MERGE requires the `;` at the end. – Jan 25 '13 at 12:35
Ah I didn't see the embedded one ;) (You don't need the one at the beginning though). – Jan 25 '13 at 12:38

score 1 · Answer 3 · answered Jan 25 '13 at 12:36

This can be done with 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;

Jacco · Answer 4 · 2013-01-25T12:42:26.663

0

You first have to cleanup your 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 this, update the rows that were left, if any:

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

And here is the Fiddle.

edited Jan 25 '13 at 12:42

answered Jan 25 '13 at 12:17

Jacco

3,251
1
19
29

You are missing A - X from the expected results. – Kaf Jan 25 '13 at 12:29

SQL: UPDATE if not exists, else DELETE

4 Answers4