Error in case when statement in sqlserver

Hi following is a working codeupdate 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 codeupdate 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.

RegardsBaiju

When you want to update only one field, you could do:

update admin
   set [add]=1
 where uname IN ('jain','baiju')
   and [add]<>1 /* no need to update records already having the correct value */
;

You could also do (just to give you an idea of other combinations):

update admin
   set [add]=CASE WHEN uname in ('jain','some other value') THEN 1 ELSE [add] END
      ,[edit]=CASE WHEN uname='baiju' THEN 1 ELSE [edit] END
 where uname IN ('jain', 'baiju','some other value')
;

I would append

OR [add] IS NULL

for Belt & Braces :slightly_smiling:

1 Like