Creating efficient Multiplication query with individual numbers in SQL Server-CodePudding

I have Table that looks like this :

Code | Column1 | Column2
A01 | 20 | NULL
A02 | 50 | NULL
A03 | 10 | NULL
A04 | 20 | NULL
A05 | 30 | NULL

And I want to update Column2 with the value of Column1 multiplied by some numbers, but only for Code A02,A03 and A04. The multiplicand here doesn't exist in a column and need to be hardcoded.

Example :

A02 = 50x20% 
A03 = 10x50% 
A04 = 20x100%

Expected Result :

Code | Column1 | Column2
A01 | 20 | NULL
A02 | 50 | 1
A03 | 10 | 5
A04 | 20 | 20
A05 | 30 | NULL

My query right now :

UPDATE A SET
Column1 = Column1*20/100 
FROM MyTable A
WHERE Code = 'A02'

UPDATE A SET
Column1 = Column1*50/100 
FROM MyTable A
WHERE Code = 'A03'

UPDATE A SET
Column1 = Column1*100/100 
FROM MyTable A
WHERE Code = 'A04'

As you can see, it's not very efficient and it's not scalable. Is there any other way to achieve my expected result more efficiently?

CodePudding user response：

Use a JOIN or a CASE expression to handle what value needs to be used instead:

UPDATE dbo.MyTable
SET Column1 = Column1 * CASE Code WHEN 'A02' THEN .2 --As this is a percentage, then use a percentage.
                                  WHEN 'A03' THEN .5 --Percentages are represented as decimals,
                                  WHEN 'A04' THEN 1. --For example, 50% is .5 and 100% is 1.
                        END
WHERE Code IN ('A02','A03','A04');
GO

UPDATE MT
SET Column1 = MT.Column1 * V.Multiplier
FROM dbo.MyTable MT
     JOIN (VALUES('A02',.2),
                 ('A03',.5),
                 ('A04',1.))V(Code,Multiplier) ON MT.Code = V.Code;

For a larger number of values I would suggest that the latter solution is more scalable, and can easily be replaced with something like a table type parameter.