Multi-table Update(MySQL)

Question

Hey all, I have a question regarding multi-table update(MySQL). Consider table t1 and t2. The PKEY for t1 is 'tid' which is a foreign Key in t2. There is a field "qtyt2" in t2 which depends on a field called "qtyt1" in table t1. Consider the foll SQL statement:

UPDATE t2,t1 SET t2.qtyt2=IF(( t2.qtyt2- t1.qtyt1 )<0,0,( t2.qtyt2- t1.qtyt1 ) ), t1.qtyt1 ="Some value.." where t2.tid="some value.." AND t2.tid=t1.tid

In this example qtyt2 depends on qtyt1 for update and the latter itself is updated.Now the result should return 2 if two rows are updated. Is there a guarantee that the fields will be updated in the order in which they appear in the statement( first qtyt2 will be set and then qtyt1).Is it possible that qtyt1 will be set first and then qtyt2? Is the order of tables in the statement important (UPDATE t2, t1 or UPDATE t1,t2)? I found that if I wrote "UPDATE t1,t2" then only t1 would get updated, but on changing the statement to "UPDATE t2,t1" everything worked correctly.

Answer 1

First off, it is always advisable to make your JOINs explicit. And 2nd, I think your condition has a typo and should be WHERE t2.qtyt2='Some value..'. So:

UPDATE t2 JOIN t1 ON (t2.tid=t1.tid)
SET t2.qtyt2= IF(( t2.qtyt2- t1.qtyt1 )<0, 0,( t2.qtyt2- t1.qtyt1 ) ), 
    t1.qtyt1 ="Some value.." 
WHERE t2.qtyt2="Some value..";

If the above is what you mean, then I believe what should be happening is that SQL will find the row set where t2.qtyt2="Some value..", then it will update t2.qtyt2, and then it will set all t1.qtyt1 (in the row set) to "Some value.." (and not the new value of t2.qtyt2).

Answer 2

I don't believe MySQL guarantees anything about the order that the updates will occur. The only time that would matter anyway is when using InnoDB tables with foreign key constraints, and the documentation specifically states that the order is not guaranteed and that the update may fail as a result. In that case, you should use separate update statements, which shouldn't be a problem because of the isolation of transactions that you get with InnoDB.