0

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.

5

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'
  );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.