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

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