Multi-table Update(MySQL)
- by smokinguns
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.
