--- In firebird-support@yahoogroups.com, Mark Rotteveel <mark@...> wrote:

>
> On 7-10-2011 11:12, tomc7777777 wrote:
> >> There's no way subselects can be materialized, at least doing ..
> >
> > My use of the term 'materialized' may have been misunderstood, it was meant to relate to whether/how the sub-select row is retrieved and compared when * vs 1 are used.
> >
> > I've a copy of Celko's 'SQL for Smarties' and he outlines the theoretical way this happens but I think says it would never happen this way in reality and is implementation-specific. If someone with intimate knowledge of the FB source (and ideally a humble disposition) could show a briefly summary of the actual steps in resolving the SQL above then that would be much appreciated.
>
> You are reversing the argument. First the row needs to be found based on
> the WHERE clause (using indices and/or natural scan of all the rows),
> and then the columns specified in the select are returned (be it a
> specific list or *). Using the reverse would be absurd, because then the
> selection of rows would depend on the columns you want to be returned
> from a row.
> --
> Mark Rotteveel
>

You are misunderstanding me. For SELECTs this was understood.

However, for UPDATES + similar statements, the scanning of the main table in relation to any sub-selects was not (i.e how the UPDATE was actually carried out) plus whether all columns were really returned or not.

e.g.

UPDATE TableA TA
SET TA.USED = 'T'
WHERE EXISTS(SELECT * FROM TableB TB WHERE TA.LINE = TB.LINE)

1. using a cursor
for each row in tablea ta
(using index or not) locate a match in tableb for TA.LINE = TB.LINE
do an update in place

2. Selects data as a completely seperate list using normal SELECT
update list as a single statement

Tom