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

• djj

  SSCoach

  Points: 18831

  More actions

  February 22, 2016 at 5:22 am

  #1860049

  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

  February 22, 2016 at 6:33 am

  #1860067

  what about simply....

  update admin

  set [add]= 1

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

• djj

  SSCoach

  Points: 18831

  More actions

  February 22, 2016 at 6:49 am

  #1860072

  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: 27795

  More actions

  February 22, 2016 at 6:35 pm

  #1860245

  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.

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