CODEWITHSUNDEEP
sqlsql-server
Kieran
Kieran

Reputation: 89

Update multiple values in a single column based on multiple criteria

I want to insert the following: 

Jan = 0.08745  
Feb = 0.07605  
Mar = 0.08745  
Apr = 0.08365  
May = 0.09125  
Jun = 0.07985  
Jul = 0.08365  
Aug = 0.08745  
Sep = 0.07224  
Oct = 0.08745  
Nov = 0.08365  
Dec = 0.07985

Into a table [Phasing] with the following columns: 

[DateKey](YYYYMM, int/not null),  
[VersionKey](XXXX, int/not null),  
[TypeKey](SmallInt, Not Null),  
[Multiplier](0.000, decimal(20,10))

I want to:

UPDATE [Phasing] 

SET [Multiplier] = 0.08745

WHERE 
[TypeKey] = 1
[VersionKey] = 1234
[DateKey] = 201801

SET [Multiplier] = 0.07605  

WHERE 
[TypeKey] = 1
[VersionKey] = 1234
[DateKey] = 201802  

etc....

I need to do this for 3x versions (1234, 4321, 9876) out of about 15.

I'm currently getting a syntax error on the first where clause: 

"Incorrect syntax near 'VersionKey'."

Upvotes: 0

Views: 1316

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

You may only do a single UPDATE per statement. So, your options are doing several separate updates, or you may do a single update using a CASE expression:

UPDATE [Phasing] 
SET [Multiplier] = CASE WHEN [DateKey] = 201801
                        THEN 0.08745
                        WHEN [DateKey] = 201802
                        THEN 0.07605 END
WHERE
    [TypeKey] = 1 AND [VersionKey] = 1234 AND
    [DateKey] IN (201801, 201802);

Upvotes: 4

Related Questions