Update same column with different values based another column

Question

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.

Why is 'A2' handled differently? If you're hard-coding , just use a where clause. SET Price = price + (price * .05) where type <> 'A2'`, etc — Andrew
– Andrew, Commented Oct 31, 2014 at 16:24

JNevill · Accepted Answer · 2014-10-31 16:29:03Z

3

Something like the following should work. I

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

edited Oct 31, 2014 at 16:29

answered Oct 31, 2014 at 16:24

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

1 Comment

Olivier Jacot-Descombes Over a year ago

Replace ";" with "," and "," with "." if you are not working with german locales: UPDATE <table> SET PRICE = IIF([TYPE] IN ("A1", "A3", "A4"), [Price] * 1.05, [Price] * 1.065)

Mureinik · Accepted Answer · 2014-10-31 16:50:25Z

2

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

edited Oct 31, 2014 at 16:50

answered Oct 31, 2014 at 16:25

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Collectives™ on Stack Overflow

Update same column with different values based another column

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related