--- In firebird-support@yahoogroups.com, wrote:

>
> Hi Alec,
>
> On 28/10/2010 18:34, Alec Swan wrote:
> > Create table MY_TAB ( id bigint, sibling_id bigint, age bigint );
> >
> > INSERT INTO MY_TAB (ID, SIBLING_ID, AGE) VALUES (1,2,10);
> > INSERT INTO MY_TAB (ID, SIBLING_ID, AGE) VALUES (2,1,20);
> >
> > update MY_TAB set age = (select t.age from MY_TAB t where t.id =
> > MY_TAB.sibling_id);
>
> updates are applied for every fetch of records from inner subselect.
> For my understanding of engine, you update statement is executed in this
> manner:
> for select id from MY_TAB into :w_id do begin
> select age from MY_TAB where sibling_id = :w_id into w_age;
> update MY_TAB set age = :w_age where id = :w_id;
> end

Correct.

...

> Call it a feature. AFAIR standard doesn't defines as this type of
> updates are to be implemented.

No, its a bug and it will be fixed in FB3. Standard explicitly said that resultset for update should be fully evaluated before start of changing records. Or, in another words, SELECT should not see changes, made by "inner" sub-statements.

Firebird's way to execute UPDATE\DELETE statements is cheaper (no need to remember whole updated resultset) and it works this way since beginning (when there was no SQL standard).

Regards,
Vlad