"Jay Blanchard" <[EMAIL PROTECTED]> wrote on 12/28/2004 11:38:58 AM:
> [snip] > And the query: > > UPDATE table1 > INNER JOIN table2 ON table2.ID_table1=table1.ID > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID IN (1,2) > > After that the value's in table2 are still 5 and 2. But I would expect > the 5 to > be a 4. > [/snip] > > It was concerning the warning I sent about InnoDB and the order of > precedence.....the query to solve the problem is here.... > > update table2, table1 > SET table2.value=table2.value-IF(table1.condition=7,1,0), > table1.condition=8 > WHERE table1.ID = table2.ID_table1 > > Note that you put table1 first in your query above, so table 1 will get > updated first. Since that is the case the conditions for updating table2 > are never met. I disagree with your logic, not your statement. It works fine. Especially when using InnoDB, the table1 values should remain their original values throughout the entire statement. The row locking should prevent a visible change in a row of table1 between SET statements in the same UPDATE. If a row has a condition value of 7 coming into the SET clause, it should have that value for every element of the SET clause. The row should only be updated when the engine moves into the next row. The cumulative changes would be committed when the transaction commits. I interpreted the warning you quoted this way: An UPDATE statement may be evaluated in a sequence that could violate FOREIGN KEY constraints because the query optimizer does not favor parent tables over child tables in computing order of operations. I did not read it as saying: the order in which you join your tables will make a difference in the evaluation of the SET assignments during the execution of an UPDATE statement. Because CHANGING THE ORDER by which the tables are joined CHANGES THE RESULTS, this is definitely a BUG. These two statements SHOULD have the same results but DO NOT: update table1, table2 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 1 Warnings: 0 update table2, table1 SET table2.value=table2.value-IF(table1.condition=7,1,0), table1.condition=8 WHERE table1.ID = table2.ID_table1; Rows matched: 4 Changed: 2 Warnings: 0 I would be deliriously happy if someone could explain WHY these statement _should_ have different results. If I am wrong and these UPDATE statements are supposed to provide different results, I would like to learn how I became so confused over something this fundamental. Until I get credible information to the contrary, I will stick with the hypothesis that this is a BUG in the execution engine. Thanks in advance, Shawn Green Database Administrator Unimin Corporation - Spruce Pine
