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

Question

I have an 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? that is, I want to update id = 3, order = 1 and id = 5, order = 4 and id = 1, order = 1

I know how to do this in three update operations. But I would like to know if I can update 3 rows in the sql update statement 1.

Thanks.

Answer 1

You can do this with a single UPDATE , but I would not worry.

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

But if you really need a single operator, you are here:

 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 2

Why do you want to update three lines in one expression?

If the lines should be synchronized with each other, you can do:

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

Thus, all the work between the beginning and fixation is either completed or nothing has been done. This is a key feature of SQL-based relational databases. Even SQLITE has this feature.

Answer 3

Try something like this:

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

Depends on your database.

Answer 4

You can use CASE if your DBMS supports it.