From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Dunlop <chris(at)onthe(dot)net(dot)au> |
Cc: | pgsql-bugs(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Update using sub-select table in schema |
Date: | 2006-10-02 15:38:45 |
Message-ID: | 11183.1159803525@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2006-10-02 17:02:55 | Re: Update using sub-select table in schema |
Previous Message | Chris Dunlop | 2006-10-02 02:19:50 | Update using sub-select table in schema |
From | Date | Subject | |
---|---|---|---|
Next Message | Strong, David | 2006-10-02 16:06:35 | Re: Faster StrNCpy |
Previous Message | Tom Lane | 2006-10-02 15:20:24 | Re: Another idea for dealing with cmin/cmax |