Update multiple values in a single column based on multiple criteria

Question

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'."

Tim Biegeleisen · Accepted Answer · 2018-05-10 00:33:36Z

4

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);

answered May 10, 2018 at 0:33

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update multiple values in a single column based on multiple criteria

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related