Update multiple values based on existing values

Question

I have a table some_table:

id | some_integer
---|-------------
 1 |      ?
 2 |      ?
 3 |      ?

How do I (and can it be done in one query) update some_table to:

Where id is 1, increase some_integer by 1
Where id is 2, decrease some_integer by 2
Where id is 3, increase some_integer by 3

To make some_table like:

id | some_integer
---|-------------
 1 |    ? + 1
 2 |    ? - 2
 3 |    ? + 3

Thanks!

Shawn · Accepted Answer · 2019-10-11 00:27:26Z

1

UPDATE some_table
SET some_integer = some_integer + (CASE id WHEN 1 THEN 1 WHEN 2 THEN -2 WHEN 3 THEN 3 END)
WHERE id IN (1,2,3);

answered Oct 11, 2019 at 0:27

Shawn

53.9k3 gold badges29 silver badges74 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2019-10-11 00:26:30Z

0

Use a CASE expression:

UPDATE yourTable
SET some_integer = CASE WHEN id = 1 THEN some_integer + 1
                        WHEN id = 2 THEN some_integer + 2
                        WHEN id = 3 THEN some_integer + 3 END
WHERE id IN (1, 2, 3);

answered Oct 11, 2019 at 0:26

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Update multiple values based on existing values

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related