Re: Why UPDATE gl SET gl.glnum = gl.glnum; cause error when UPDATE gl

I have a construct where column has the same name .. and when I use the
FROM clause I get the following error:

create table test (
id varchar(8)
);

create table test2 (
id varchar(8)
);

update test set id=test2.id from test2 where id=test2.id;

ERROR: column reference "id" is ambiguous

/David
P.S.: It's just an example .. I rename the column in the real statement
to make it works.

Bruno Wolff III wrote:

>On Fri, Jul 08, 2005 at 09:59:03 -0400,
> David Gagnon <dgagnon(at)siunik(dot)com> wrote:
>
>
>>Hi all,
>>
>>I was juste wondering why the following code don't work:
>>
>>
>
>Because the value being set is a column name from the table being updated
>and you aren't allowed to qualify it with a table name.
>
>You don't really want to use the table name of the right side either as
>that will result in gl being joined to itself and will not give you the
>results you expect. (This is using the implied from feature which is
>enabled by default in 8.0.* or less, but will be disabled by default
>in 8.1.)
>
>
>
>>UPDATE gl SET gl.glnum = gl.glnum
>>ERROR: column "gl" of relation "gl" does not exist
>>
>>While the following works:
>>UPDATE gl SET glnum = glnum;
>>
>>Query returned successfully: 177 rows affected, 281 ms execution time.
>>
>>the TABLE.COLUMN is not in the SQL standard ?
>>
>>Thanks
>>/David
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>>
>
>
>