We help IT Professionals succeed at work.

update sql query with case statement

PratikShah111
on
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
Comment
Watch Question

SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
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