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

Question

Hi,

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 use CASE if your DBMS supports it.

Answer 2

Try something like this:

update Orders
set 
  Order = (
    case 
      when id = 3 then 1
      when id = 5 then 4
      when id = 1 then 1
where id in (3, 5, 1)

Depends on your database though.

Answer 3

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)

Answer 4

Why do you want to update three rows in one statement?

If the rows must all be in sync with one another, you can do:

BEGIN TRANSACTION;
UPDATE... ;
UPDATE... ;
UPDATE... ;
COMMIT

That way, all work between the begin and commit, is either all completed or none of it is done. This is a key feature of SQL-based relational databases. Even SQLITE has this capability.