I'm using MySQL InnoDB.

I'd like to update a table and control the sequence that each column is updated. All in one query if possible. The reason is that I have a queue system for a game. When you finish queue_1 I want it to be assigned the value of queue_2, then erase queue_2.

Right now I'm getting unpredictable results from this. Sometimes total_price_2 is set to zero, THEN loaded into total_price_1. Both becoming zero. (Not what I want.)

I read that the DB decides what order to run the updates. If I have to do two updates that's fine. My goal is performance.

UPDATE
queue
SET     
queue_1 = queue_2,
total_price_1 = total_price_2,
total_wait_1 = total_wait_2,
queue_2 = '',
total_price_2 = 0,
total_wait_2 = 0 
WHERE id IN(1,2,3)

• mysql

Check that total_price_2 wasn't 0 before the update, as suggested by MichaelRushton.

In MySQL, single-table UPDATE queries are processed from 'left to right', which is stated in the UPDATE statement documentation:

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order. (Source)

Specifically, note this example:

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Side Note

As a bit of a side note, you can also explicitly specify the order for rows to be updated by using the ORDER BY clause:

With no WHERE clause, all rows are updated. If the ORDER BY clause is specified, the rows are updated in the order that is specified. (Source)

Split your update query for queue_1 and queue_2.

Make one update query for queue_1 and another for queue_2, because MySQL gets confused when it updates values, and it updates queue_2 before queue_1, which makes your result 0.

It's worth noting that if you are depending on server-dependent behaviour, such as implied by This behavior differs from standard SQL, you need to be careful in the future. What if you change DB? Or someone else updates that code without realising the importance of the ordering?

You might be better off splitting the update into two clear statements, and using a transaction to ensure they run atomically.