RE: Multi table update

[Jay Blanchard]

[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. 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. Since I have
been using SQL UPDATES and DELETES have been handled in JOIN order in
multi-table operations.