On Mon, 2006-10-02 at 11:38 -0400, Tom Lane wrote:
> Chris Dunlop <[EMAIL PROTECTED]> writes:
> > I'm not sure if this is a bug or if it's displaying my ignorance
> > of this corner of SQL...
> > update a set name = (
> >   select name
> >   from temp.a
> >   where temp.a.id = a.id
> > )
> Postgres treats "FROM temp.a" the same as "FROM temp.a AS a", and then
> the "a.id" references that rather than the outer query's A.  Try just
> "select name from temp.a where temp.a.id = a.id;" to see this in action.

[Which AFAICS doesn't follow SQL:2003, since a reference to temp.a
should not be allowed following its redefinition as a.]

> Looking at the SQL spec, I'm having a hard time finding any verbiage
> that either confirms or denies this interpretation.  It says that a FROM
> item without a correlation name (ie, an AS alias) exposes the table
> name, and then a column reference can refer to the table name, but it's
> not at all clear whether the table name must be qualified or not in the
> reference.  Comments anyone?

In my SQL:2003 draft, there is a "Language Opportunity" here:

"242 [From London] The following Opportunity exists:
   For language consistency, a correlation name should be permitted for
the modiļ¬ed table in positioned and searched update and delete

However, somebody claiming to be Joe Celko is quoted here as saying that
is not part of the SQL:2003 standard

ISTM the most obvious route in this situation differs from normal usage:
treat any unqualified names that match the target table as a reference
to the target table, rather than potentially another table. i.e. treat
this situation as a correlated sub-query rather than as an independent

No correlation name on the target table is allowed, so there is no
possibility of writing a correlation name to allow the query to be more

> update a correlationname set name = (
>    select name
>    from temp.a
>    where temp.a.id = correlationname.a.id
> )

(which is definitely not allowed by SQL:2003)

Having said all of that, its clearly a grey area so no need to change
this as part of beta, since we could easily cause more wierdness than we

  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?


Reply via email to