?
Solved

update sql query with case statement

Posted on 2016-07-29
1
Medium Priority
?
83 Views
Last Modified: 2016-07-29
I have the following select query

select
w.login,
w.RolesSetThru,
e.Authenticity as eAuthenticity,
a.Authenticity as aAuthenticity ,
case when e.Authenticity in (1,3) then 'AD'
     when a.Authenticity in (1,3) then 'AD'
else
      'ERMXref'
end            as Authenticity  
from  controls..WrkUser w
left outer join controls..WrkERMGroupMember e on
 w.Login = e.Login
left outer join controls..WrkADGroupMember a on
 w.Login = a.Login

I want to update w.RolesSetThru column with the values that I get from case statement. Can someone please show me how I can get that done
0
Comment
Question by:PratikShah111
1 Comment
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 41735112
You can't do a SELECT and UPDATE in the same statement, so you'll have to borrow from this SELECT  and write an UPDATE.

Give this a whirl..
UPDATE w
SET w.RolesSetThru = case 
   when e.Authenticity in (1,3) then 'AD'
   when a.Authenticity in (1,3) then 'AD'
   else  'ERMXref' end       
FROM  controls..WrkUser w
   LEFT JOIN controls..WrkERMGroupMember e on w.Login = e.Login
   LEFT JOIN controls..WrkADGroupMember a on w.Login = a.Login

Open in new window

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question