Re: "Transactions are bad, real bad" - discuss

"Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message news:b8ljgd$383i$3_at_gazette.almaden.ibm.com...
> As Date/Darwen would tell you, the above is in fact, i.e. is logically,
one
> single update. In this example, even SQL (!) can treat is as so:
>
> UPDATE Accounts
> SET Balance = CASE WHEN AccountOwner = 'Paul Vernon' THEN
> Balance - 1000000
> WHEN AccountOwner = 'Jonathan Leffler' THEN
> Balance + 1000000
> END
> WHERE AccountOwner IN ('Paul Vernon' , 'Jonathan Leffler' )
>
> The traditional problem has been when the update covers more than one
table.
> SQL does not have a 'multiple update' syntax, so it is forced to use a
> transaction. But SQL is not the limit of the relational model.

How about

UPDATE (
    select a1.AccountOwner ao1, a1.Balance b1, a2.AccountOwner ao2, a2.Balance b2

    from Accounts as a1, Accounts as a2
) set b1 = b1 - 1000000, b2 = b2 + 1000000 where ao1 = 'Paul Vernon' and ao2 = 'Jonathan Leffler' Received on Wed Apr 30 2003 - 00:04:16 CEST