[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 80
  • Last Modified:

update sql query with case statement

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
PratikShah111
Asked:
PratikShah111
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now