Why not UPDATE two tables in one statement?

[rac2]

Is there an ANSI standard that the UPDATE statement operates on one table.

And if so, why this limitation?

 

[r937]

yes, because that's just the way UPDATE works

what did you need to do? i mean, what limitation are you experiencing? transaction block? roll back both updates if either fails?

rudy

 

[IanGlinka]

Heh... that's 'just the way UPDATE works'... Well, thanks rac2. That does seem to be quite an, irritating, limitation.

Ian

 

[mwolf00]

The "limitation" is easily overcome by writing an ON UPDATE trigger..... -- What did you expect? This is FREE advice. LOL

 

[dickiebird]

Writing a trigger to insert into table-2 after insert into Table-One would achieve the required effect, wouldn't it?
;-) Dickie Bird
Honi soit qui mal y pense

 

[AME]

Allowing more than one table to be updated in the same UPDATE statement would be very useful sometimes.
It would, for example, be nice to avoid that a costly WHERE-clause is evaluated more than once.

However, there are some special problems that would have to be solved for such an implementation.

A straight forward implementation could, for example, support the following statement:
update T1,T2 set T1.C1 = T2.C1
where T1.PK = 1 and T2.PK in (1,2)

I am still updating only one table, but the search condition is really doing a join. The problem is that I may find two rows in T2 for the same row in T1. Should the T1.C1 value be set to the T2.C1 value corresponding to T2.PK = 1 or to T2.PK = 2.


/AME