Multi table UPDATE sequence of SET in MYSQL

Question

I'm implementing mysql query where I am updating multiple tables with conditions and I'm struggling with documentation (https://dev.mysql.com/doc/refman/9.0/en/update.html_ ). First off for anyone who are not aware with mysql peculiarity:

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;

My main problem is this statement "For multiple-table updates, there is no guarantee that assignments are carried out in any particular order".

I got to this point after my query for chat app wasn't working properly:

UPDATE chat_user cu 
 LEFT JOIN user a ON cu.chat_user__user_id = a.user__id 
 SET 
 a.user__message_not_seen_count = IF(cu.chat_user__main_last_received_microtimestamp > cu.chat_user__last_seen_microtimestamp, a.user__message_not_seen_count - 1, a.user__message_not_seen_count), 
 cu.chat_user__last_seen_microtimestamp = IF(cu.chat_user__last_seen_microtimestamp > $microtime_seen, cu.chat_user__last_seen_microtimestamp, $microtime_seen) 
 WHERE cu.chat_user__id = $chat_user_id;

It would not decrease my counter as I assumed update operation "left to right" but this turns out only applies to single table udpates.

However when I switch tables it seems to work fine (I assume main table is being updated first)

UPDATE user a 
 LEFT JOIN chat_user cu ON cu.chat_user__user_id = a.user__id 
 SET 
 a.user__message_not_seen_count = IF(cu.chat_user__main_last_received_microtimestamp > cu.chat_user__last_seen_microtimestamp, a.user__message_not_seen_count - 1, a.user__message_not_seen_count), 
 cu.chat_user__last_seen_microtimestamp = IF(cu.chat_user__last_seen_microtimestamp > $microtime_seen, cu.chat_user__last_seen_microtimestamp, $microtime_seen) 
 WHERE cu.chat_user__id = $chat_user_id;

Interestingly where both tables are joined then it also seems to work fine as if after main table is updated then joined tables are updated "left to right"

UPDATE chat c 
 LEFT JOIN chat_user cu ON cu.chat_user__chat_id = c.chat__id 
 LEFT JOIN user a ON cu.chat_user__user_id = a.user__id 
 SET 
 a.user__message_not_seen_count = IF(cu.chat_user__main_last_received_microtimestamp > cu.chat_user__last_seen_microtimestamp, a.user__message_not_seen_count - 1, a.user__message_not_seen_count), 
 cu.chat_user__last_seen_microtimestamp = IF(cu.chat_user__last_seen_microtimestamp > $microtime_seen, cu.chat_user__last_seen_microtimestamp, $microtime_seen) 
 WHERE cu.chat_user__id = $chat_user_id;

Naturally my question is if there is underlying database engine mechanics that is stable meaning I can rely on the 2nd and 3rd query or I should take "in any particular order" literally meaning that it can randomly break at any time and look for other solutions? If so maybe any ideas how could I implement a different solution? I assume using multiple queries in single transaction might be the only option? Thank you.

• Charlieface: "I should take "in any particular order" literally meaning that it can randomly break at any time" probably. I doubt anyone can give you a definitive answer, even knowing the source code won't help as it's subject to change without notice. Consider redesigning your DB so you don't need to store the same info in multiple places.
• Akina: if there is underlying database engine mechanics that is stable Yes. STRAIGHT_JOIN fixes the tables scanning order and results in stable calculations ordering.. but it may vary from version to version - i.e. you'd test.

Answer 1

When the documentation uses language like "for multiple-table updates, there is no guarantee that assignments are carried out in any particular order," they are telling you that the order can change and you shouldn't depend on it.

It might change when the optimizer reorders your tables because it estimates it'll get a more efficient query by doing so.

It might change in a subsequent version of MySQL, if they change any code in the optimizer.

If you need to do a complex change, do it in a transaction.

You can issue multiple UPDATE statements serially, to do multiple changes.

Even if the order remains stable in the query you show, it may not behave the same in a different query, and you'd have to test each query very carefully. The next programmer who works on your code won't know which ones are stable and may not know how to test them. For this reason, you should try to write code clearly, so someone with less practice than you can take over maintenance.

• Otto: > If you need to do a complex change, do it in a transaction. This is the gist of it!
• Shadur-don't-feed-the-AI: +1. Writing clear code >>>>> writing "optimized" code that Future You will not understand and spend days fruitlessly trying to debug/modify, only to give up and rewrite the whole thing from scratch.