Sql-server – Update if not exists statement

sql serversql server 2014t-sqlupdate

If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict?

Here's a simplified example.

Take the following table:

User  | Role
------|----------------
Joe   | User
Joe   | Administrator
Brian | User
Anna  | Administrator
Anna  | Admin

Suppose I want to write the following query:

UPDATE UserRoles Set Role = 'Admin' WHERE Role = 'Administrator'

Without using cursors, how do I change my SQL Update statement to avoid renaming Anna (because that user already has the Admin role), but still rename Joe?

Is that even possible using set based SQL or is using a cursor the only way?

Thanks.

Best Answer

One way is 

UPDATE a
SET [Role]= 'Admin' 
FROM UserRoles a
WHERE a.[Role]= 'Administrator'
AND NOT EXISTS 
  (
       SELECT NULL 
       FROM dbo.UserRoles b 
       WHERE b.[User] = a.[User] AND b.[Role] = 'Admin'
  );
Related Question