Re: request for sql3 compliance for the update command

Tom Lane kirjutas K, 19.03.2003 kell 16:46:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I wasn't sure it made logical sense to allow correlated subqueries in
> > FROM because the FROM is processed before the WHERE.
>
> It doesn't; in fact it violates the whole semantic model of SQL,
> as far as I can see. Sub-selects in FROM are (in principle)
> evaluated separately and then joined. They can't have cross-references.

Makes sense. What I was describing would have been akin to updatable
queries where you first do all the joining and then update one of the
underlying tables.

the more accurate (nonstandard) syntax could have been

SELECT src.val,
tgt.val
FROM updatesrc as src FOR UPDATE,
updatetgd as tgt
WHERE src.id = tgt.id
SET src.val = tgt.val
;

> I think there is some weird construct in SQL99 that alters this behavior,
> though.

You probably mean WITH, which acts like FROM but has lexically previous
(or all in case of WITH RECURSIVE) sub-selects in its namespace.

----------------
Hannu