Is it possible to update 3 sql rows in 1 sql update statement

Question

I have a SQL table like this.

id          Order
======== =========
1                4
2                3
3                5
4                1
5                2

Is it possible to update multiple rows in 1 sql statement? i.e. I want to update id = 3, order = 1 and id = 5, order = 4 and id = 1, order = 1

I know how to do that in 3 update statements. But I would like to know if I can update 3 rows in 1 sql update statement.

Thank you.

Answer 1

You can do this with a single UPDATE statement, but I wouldn't bother.

It makes more sense to use three separate updates in this situation. Trying to do it with one statement makes your code less readable and more error-prone.

But if you really want the single statement, here you go:

UPDATE your_table
SET order = CASE id
                WHEN 3 THEN 1
                WHEN 5 THEN 4
                WHEN 1 THEN 1
            END
WHERE id IN (3, 5, 1)