Update if not exists statement

Question

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.

Paul White · Accepted Answer · 2019-02-20 03:54:11Z

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

Stack Exchange Network

Update if not exists statement

1 Answer 1

Hot Network Questions

Update if not exists statement

1 Answer 1

Related

Hot Network Questions