case when error in sqlserver

Question

case when error in sqlserver

baiju krishnan

SSC Veteran

Points: 288
More actions
February 21, 2016 at 4:11 am

#324435

Hi following is a working code
update admin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[edit] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
where uname IN ('jain', 'baiju')
this statement is for updating add=1 and edit=1 for users 'jain' and 'baiju'
some times i want to update add=1 for both unames.then i tried this code
update admin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[add] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
where uname IN ('jain', 'baiju')
but this is not possible.
how to write the above code using case when. because iam using multiple unames with different fields.
Regards
Baiju

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

djj SSCoach Points: 18831 More actions · Answer 1

baiju krishnan (2/21/2016)
Hi following is a working code
update admin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[edit] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
where uname IN ('jain', 'baiju')
this statement is for updating add=1 and edit=1 for users 'jain' and 'baiju'
some times i want to update add=1 for both unames.then i tried this code
update admin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[add] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
where uname IN ('jain', 'baiju')
but this is not possible.
how to write the above code using case when. because iam using multiple unames with different fields.
Regards
Baiju

Try

update admin

set [add]= CASE WHEN uname = 'jain' THEN 1 WHEN uname='baiju' THEN 1 ELSE [add] END

where uname IN ('jain', 'baiju')

--OR

update admin

set [add]= CASE WHEN uname = 'jain' OR uname='baiju' THEN 1 ELSE [add] END

where uname IN ('jain', 'baiju')

David McKinney SSChampion Points: 10472 More actions · Answer 2

what about simply....

update admin

set [add]= 1

where uname IN ('jain', 'baiju')

djj SSCoach Points: 18831 More actions · Answer 3

David McKinney (2/22/2016)
what about simply....
update admin
set [add]= 1
where uname IN ('jain', 'baiju')

I guess I cannot see the forest for the trees. :hehe:

MMartin1 One Orange Chip Points: 27768 More actions · Answer 4

updateadmin
set [add]= CASE WHEN uname = 'jain' THEN 1 ELSE [add] END
,[ADD] = CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
whereuname IN ('jain', 'baiju')

This only gets the unames in Jain and Baiju. So [add] is guaranteed to = 1 only.

Is this what you are after ...

updateadmin

set [ADD] = CASE when uname IN ('jain', 'baiju') THen 1 ELSE [edit] END

I suggest you post create statements for tables and sample data. Also include an explanation of what you want the final output to look like and the business reason for that.

----------------------------------------------------