Updating multiple tables (MySQL)
I have a question about multi-stage upgrade (MySQL). Consider the table t1 and t2. The PKEY for t1 is "tid", which is a foreign key in t2. There is a field "qtyt2" in t2 that depends on a field called "qtyt1" in table t1. Consider the following 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 to update, and the latter updates itself. The result should now return 2 if two rows are updated.
Is there any guarantee that the fields will be updated in the order in which they appear in the statement (the first qtyt2 will be installed and then qtyt1)?
Is it possible that qtyt1 will be installed first and then qtyt2?
Is the order of the 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 be updated, but changing the statement to "UPDATE t2, t1" everything worked correctly.
source to share
First, it is always advisable to keep your JOINs clear. And 2nd, I think your state 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 you say above, then I suppose this should happen is that SQL will find the rowset where t2.qtyt2="Some value.."
, then it will update t2.qtyt2, and then set all t1.qtyt1 (to the rowset) to "Some value. . "(not the new t2.qtyt2 value).
source to share
I do not believe that MySQL does not guarantee anything that updates will happen. The only point that matters anyway is to use InnoDB tables with foreign key constraints, and the documentation states that the order is not guaranteed and that the update might fail as a result. In this case, you should use separate update statements, which shouldn't be a problem due to the isolation of transactions you get with InnoDB.
source to share