CODEWITHSUNDEEP
sql
Laughy
Laughy

Reputation: 1979

if else statement with set condition (SQL)

just wondering, is there a reason why the below code doesn't work? I am trying to update 2 column values if they hit multiple criteria but I got an error that says 'Incorrect syntax near the keyword 'IF' and 'Incorrect syntax near 'Total ' . Any help greatly appreciated!

update  [dbo].[wages table]
IF([criteria1]='AA1' and [criteria2]='50yrs and below' and [criteria3]= 'X<=50') 
set [Total ] = '0', [Employee ]='0' else
if ([criteria1]='AA1' and [criteria2]='50yrs and below' and [criteria3]= 'X<=50') 
set [Total ] = '10', [Employee ]='10' else
if ([criteria1]='AA1' and [criteria2]='50yrs and below' and [criteria3]= 'X<=50') 
set [Total ] = '20', [Employee ]='20' else
if ([criteria1]='AA1' and [criteria2]='50yrs and below' and [criteria3]= 'X<=50') 
set [Total ] = '30', [Employee ]='30' else
set [Total ] = 'NULL', [Employee ]='NULL'

Upvotes: 0

Views: 72

Answers (2)

Raging Bull
Raging Bull

Reputation: 18737

I guess this is what you are trying to do:

UPDATE [dbo].[wages table]
   SET [Total] = CASE [criteria3] WHEN 'X<=50' THEN '0' ELSE 'NULL' END,
   SET [Employee] = CASE [criteria3] WHEN = 'X<=50' THEN '0' ELSE 'NULL' END
WHERE [criteria1]='AA1' and [criteria2]='50yrs and below'

If criteria1 and criteria2 are same for all then you can check it in WHERE clause. I believe the difference is in criteria3.

Something like:

UPDATE [dbo].[wages table]
   SET [Total] = CASE [criteria3] WHEN 'X<=50' THEN '0' WHEN 'X<=40' THEN 1 WHEN 'X<=30' THEN 3 ELSE 'NULL' END,
   SET [Employee] = CASE [criteria3] WHEN 'X<=50' THEN '0' WHEN 'X<=40' THEN 1 WHEN 'X<=30' THEN 3 ELSE 'NULL' END
WHERE [criteria1]='AA1' and [criteria2]='50yrs and below'

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

In SQL you can you CASE expressions for this type of statements:

UPDATE  [dbo].[wages table]
SET [Total] = CASE WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   ELSE NULL END,
    [Employee ] = CASE WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   WHEN ([criteria1]='AA1' AND [criteria2]='50yrs and below' AND [criteria3]= 'X<=50')  THEN '0' 
                   ELSE NULL END

Besides, I can't see any difference in your conditions.

Upvotes: 2

Related Questions