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@siunik.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