Error in case when statement in sql server

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

Answer 1

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

Answer 2

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

Answer 3

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.