Zitat von Jay Blanchard <[EMAIL PROTECTED]>: > [snip] > Although my question goes in that direction, it is more about why this > statement > does not act atomically from my applications point of view. > > > update table2, table1 > > SET table2.value=table2.value-IF(table1.condition=7,1,0), > > table1.condition=8 > > WHERE table1.ID = table2.ID_table1 > [/snip] > > Define "atomically". If your definition is that each value in each table > is updated simultaneously that would be incorrect.
"atomically" from my applications point of view means: I have a snapshot of my data (due to InnoDB this does not change hiddenly during my session). I send a query to the database. I get the result / changed data. And everything between sending and the receiving the result I expect to be handled at once. I know MySQL cannot handle handle the query as one big block behind the doors, but I wonder why I / my application has to know about these internals in this case. > UPDATE statements > work with the JOIN order and update each table in succession. Dependent > on the number of rows affected you may see both tables locked > (transactionally) but note a delay in the update of table1. My application does not see this and that's my point. In case of this query I have to know, that there is more than the data before the query and the data after the query. There is some intermediate state that I must be aware of. Take it this way: It does not matter if I write SELECT IF(table1.condition=7,1,0) FROM table1,table2 WHERE table1.ID=table2.ID_table1 or SELECT IF(table1.condition=7,1,0) FROM table2,table1 WHERE table1.ID=table2.ID_table1 The rows might be in a different order, but the rows I get are all the same in both cases. The same IF-statement in an UPDATE changes its value silently, if I change the order in which I join. Greetings Kai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
