SQLTeam.com | Weblogs | Forums

Error in case when statement in sqlserver

sql2008r2

#1

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


#2

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')
;

#3

I would append

OR [add] IS NULL

for Belt & Braces :slightly_smiling: