Geoff Winkless <pgsqladmin@geoff.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>
<rightbracket 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
