locked
Error in case when statement in sql server RRS feed

  • Question

  • User1839056048 posted

    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

    Sunday, February 21, 2016 11:07 AM

Answers

  • User372437940 posted

    Hi,

    In your example the Case is not necessary because the Where clause restricts the names, but you could use the In operator. Ex:

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

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 21, 2016 1:44 PM

All replies

  • User372437940 posted

    Hi,

    In your example the Case is not necessary because the Where clause restricts the names, but you could use the In operator. Ex:

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

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, February 21, 2016 1:44 PM
  • User-698989805 posted

    Check these links to clarify:

    http://stackoverflow.com/questions/4097939/case-statement-with-in-in-where-clause

    http://stackoverflow.com/questions/15766102/i-want-to-use-case-statement-to-update-some-records-in-sql-server-2005

    Sunday, February 21, 2016 4:19 PM
  • User753101303 posted

    Hi,

    Or here you could just a single statement :

    UPDATE admin SET [add]=1 WHERE uname IN ('jain', 'baiju') rather than 2 statements as I suggested earlier. IMO this kind of trick just make things more complex than needed. Just create the statement(s) you need and that's it.

    Not directly related but if you are just starting with db access you may want to have a look at https://msdn.microsoft.com/en-us/data/ef.aspx. It allows to expose db data as .NET objects to your own code.

    Monday, February 22, 2016 4:20 PM