Sfrgttk

Copy the content of an environment

Does human life have innate value over that of other animals?

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

Why is it a problem for Freddie if the guys from Munich did what he wanted?

Why does RAM (any type) access time decrease so slowly?

I'm struggling to say 'struggle'

How to explain one side of Super Earth is smoother than the other side?

Should corporate security training be tailored based on a users' job role?

Why does calling Python's 'magic method' not do type conversion like it would for the corresponding operator?

Publication rates for different areas of mathematics?

The idea behind the state of Imperative languages

Would life expectancy increase if we replaced healthy organs with artificial ones?

What have we got?

Is it appropriate to give a culturally-traditional gift to a female coworker?

How can I portray body horror and still be sensitive to people with disabilities?

How to know if I am a 'Real Developer'

How to not forget my phone in the bathroom?

Buying a "Used" Router

Why do most space probes survive for far longer than they were designed for?

Is layered encryption more secure than long passwords?

"Cheaper by the dozen" phrase origin?

Apparently I’m calling random numbers but nothing in call log?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Ethernet cable only works in certain positions

Update if not exists statement

Deadlock on Insert/UpdateSet replica set member host names on SecondaryHow to update ID=null values in table to incremental counter values?How to UPDATE/DELETE/INSERT with Microsoft.ACE.OLEDB.12.0 as Linked Server in SQL Server 2012?Update all rows from a table with random foreign key from another tableHow to use set-based statement instead of cursor for converting 1 table to two, keeping relationsConfiguring Custom Hierarchical Reporting RolesPostgreSQL Partitioned table update triggerPass data from a table row into a create login statement using dynamicSQLHow to optimize UPDATE with a nested SELECT subquery?

0

1

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.

sql-server t-sql sql-server-2014 update

share|improve this question

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

add a comment | 

0

1

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.

sql-server t-sql sql-server-2014 update

share|improve this question

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

add a comment | 

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.

sql-server t-sql sql-server-2014 update

share|improve this question

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

User  | Role

------|----------------

Joe   | User

Joe   | Administrator

Brian | User

Anna  | Administrator

Anna  | Admin

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

share|improve this question

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

share|improve this question

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

asked Dec 10 '15 at 23:20

Lambo JayapalanLambo Jayapalan

102127

1 Answer
1

active

oldest

votes

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'

  );

share|improve this answer

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f123420%2fupdate-if-not-exists-statement%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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'

  );

share|improve this answer

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

add a comment | 

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'

  );

share|improve this answer

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

add a comment | 

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'

  );

share|improve this answer

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

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'

  );

share|improve this answer

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

edited 7 mins ago

Paul White♦

52.1k14278450

edited 7 mins ago

Paul White♦

52.1k14278450

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632

answered Dec 10 '15 at 23:32

a1ex07a1ex07

7,32621632