Re: Update using sub-select table in schema

Chris Dunlop <chris(at)onthe(dot)net(dot)au> 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.

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?

regards, tom lane