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?