Re: Why the "UPDATE tab SET tab.col" is invalid?

Geoff Winkless <pgsqladmin(at)geoff(dot)dj> writes:
> I grabbed the wrong section of the doc; I should of course have pasted
> the searched version:

> <update statement: searched> ::=
> UPDATE <table name>
> SET <set clause list>
> [ WHERE <search condition> ]

> My point is still the same though :)

Don't know which version of the SQL spec you're looking at, but SQL:2008
has

<update statement: searched> ::=
UPDATE <target table> [ [ AS ] <correlation name> ]
SET <set clause list>
[ WHERE <search condition> ]

Note the [ [ AS ] <correlation name> ] bit. However, they do NOT
allow the correlation name to appear in <set target>:

<set clause list> ::=
<set clause> [ { <comma> <set clause> }... ]

<set clause> ::=
<multiple column assignment>
| <set target> <equals operator> <update source>

<set target> ::=
<update target>
| <mutated set clause>

<multiple column assignment> ::=
<set target list> <equals operator> <assigned row>

<set target list> ::=
<left paren> <set target> [ { <comma> <set target> }... ] <right paren>

<assigned row> ::=
<contextually typed row value expression>

<update target> ::=
<object column>
| <object column>
<left bracket or trigraph> <simple value specification> <right bracket or trigraph>

<object column> ::=
<column name>

<mutated set clause> ::=
<mutated target> <period> <method name>

<mutated target> ::=
<object column>
| <mutated set clause>

<update source> ::=
<value expression>
| <contextually typed value specification>

<column name> is elsewhere defined as just <identifier>, if you were
hoping there was more there than meets the eye. The "mutated target"
business is some overly complex version of composite-type columns.

The reason why SQL doesn't allow an optional correlation name, and
probably never will, is the same as the reason why we don't, and probably
never will: it introduces an ambiguity as to whether you meant a dotted
set-clause target name to be a reference to a field of a composite column
or just a noise-word reference to the table's correlation name. If there
were any functional value in specifying the correlation name, it might be
worth dealing with the ambiguity; but there isn't.

regards, tom lane