CODEWITHSUNDEEP
sqlms-access
Babidi
Babidi

Reputation: 74

Update same column with different values based another column

I have this table

Type   Price
A1     900
A2     800
A3     700
A4     600

I want to execute a update query where the Prices for A1, A3 and A4 increased by 5% The price for A2 must be increased with 6,5%

I tried to use Case or IIF and many more. But i cant figure out how i can put this in one query.

Upvotes: 1

Views: 1130

Answers (2)

Mureinik
Mureinik

Reputation: 311163

You can use a switch expression:

UPDATE my_table
SET    price = price * SWITCH (
                          type IN ('A1', 'A3'), 1.05,
                          type = 'A4', 1.065)
WHERE  type IN ('A1', 'A3', 'A4')

Upvotes: 2

JNevill
JNevill

Reputation: 50034

Something like the following should work. I

UPDATE <table>
SET PRICE = IIF([TYPE] IN ("A1","A3", "A4"); [Price]*1,05; [Price]*1,065)

Upvotes: 3

Related Questions