List:General Discussion« Previous MessageNext Message »
From:Jay Blanchard Date:December 29 2004 1:22pm
Subject:RE: Multi table update
View as plain text  
[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.
Thread
importing dataCurtis Maurand28 Dec
  • Re: importing dataMichael J. Pawlowsky28 Dec
    • Re: importing dataKai Ruhnau28 Dec
      • Re: importing dataSGreen28 Dec
        • Re: Multi table updateKai Ruhnau28 Dec
          • Re: Multi table updateSGreen28 Dec
            • Re: Multi table updateKai Ruhnau28 Dec
              • Re: Multi table updateSGreen28 Dec
    • Re: importing dataCurtis Maurand28 Dec
      • Re: importing dataCurtis Maurand28 Dec
RE: Multi table updateJay Blanchard28 Dec
RE: Multi table updateJay Blanchard28 Dec
  • RE: Multi table updateSGreen28 Dec
Re: Multi table updateKai Ruhnau28 Dec
RE: Multi table updateJay Blanchard29 Dec
RE: Multi table updateJay Blanchard29 Dec
  • RE: Multi table updateKai Ruhnau29 Dec
RE: Multi table updateJay Blanchard30 Dec
  • RE: Multi table updateSGreen30 Dec
    • Re: Multi table updateKai Ruhnau30 Dec
RE: Multi table updateJay Blanchard30 Dec
  • Re: Multi table updateKai Ruhnau30 Dec
  • RE: Multi table updateSGreen30 Dec
RE: Multi table updateJay Blanchard30 Dec
RE: Multi table updateJay Blanchard30 Dec
  • RE: Multi table updateSGreen30 Dec
RE: Multi table updateJay Blanchard30 Dec
  • RE: Multi table updateSGreen30 Dec
RE: Multi table updateJay Blanchard30 Dec
  • RE: Multi table updateSGreen31 Dec
RE: Multi table updateJay Blanchard3 Jan