Hi, Arno!

> UPDATE
> Table1 K
> SET
> Field = (SELECT T.Field2 FROM Table2 T
> WHERE K.KeyField = T.KeyField and T.FieldX = Value) WHERE
> EXISTS(SELECT 1 FROM Table2 T
> WHERE K.KeyField = T.KeyField and T.FieldX = Value)
>
> I understand that the proposed UPDATE should be more effective.

I cannot be so sure about that.
Standard syntax for UPDATE and DELETE statement was designed to work
correctly and without update conflicts with READ COMMITTED isolation
level.

If record changes in between you read and update it you can re-read the
record, re-evaluate boolean condition for this record and re-execute
expressions in SET clause.
This is the approach how pessimistic locking (WITH LOCK) works now (it
only doesn't have SET clause).

Normal Firebird updates and deletes inherited GDML design which doesn't
follow this pattern and tends to raise unnecessary "update conflict"
errors, but this is a bug which has to be fixed eventually. Now this is
one of the major problems you have to address when migrating serious
applications from Oracle to Firebird.

But if we add this MS extension allowing joins in updates and deletes
we're about to force us to live with this limitation forever or use
record locking via lock manager like MS does.
Both Oracle and Firebird use record header for update locks and
downgrading it to extensive use of lock manager would be unwise to say
the least.

In short, the MS extension for using joins in UPDATE and DELETE
statement contradicts with proper MGA design.
For Firebird it would be possible to implement it "properly" either via
migrating to mandatory record locking via lock manager which sounds like
a step backwards or adding join syntax limitations so those joins may be
rewritten as a single boolean by the optimizer which sounds and looks
really ugly.

I think we better not go there.

> Arno Brinkman

Nickolay