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

On Thu, Apr 7, 2016 at 4:39 AM, postgres_sure <postgres_sure@163.com> wrote:
> Hi,
>
> I found "Do not include the table's name in the specification of a target
> column
>                  — for example, UPDATE tab SET tab.col = 1 is invalid." in
> the documentation.
>
> Some people usually like to update table by alias. They want to add this
> feature.

Is this syntax described in the SQL standard?

merlin

On 7 April 2016 at 14:48, Merlin Moncure <mmoncure@gmail.com> wrote:
>
> On Thu, Apr 7, 2016 at 4:39 AM, postgres_sure <postgres_sure@163.com> wrote:
> > Hi,
> >
> > I found "Do not include the table's name in the specification of a target
> > column
> >                  — for example, UPDATE tab SET tab.col = 1 is invalid." in
> > the documentation.
> >
> > Some people usually like to update table by alias. They want to add this
> > feature.
>
> Is this syntax described in the SQL standard?

Given that the SQL standard has no concept of UPDATEs against a query
with multiple tables, and that's where this could bring the most
clarity, I'm not sure that it's reasonable to object on that basis.

As far as I can see the SQL standard doesn't let you alias tables in
an UPDATE (for the same reason, I imagine) but we allow _that_ even in
a single-table UPDATE.

<update statement: positioned> ::=             UPDATE <table name>               SET <set clause list>
WHERECURRENT OF <cursor name> 


Geoff

On 7 April 2016 at 15:51, I wrote:
> <update statement: positioned> ::=
>               UPDATE <table name>
>                 SET <set clause list>
>                   WHERE CURRENT OF <cursor name>

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 :)

Geoff

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

On 7 April 2016 at 16:45, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Don't know which version of the SQL spec you're looking at,

It was the draft 95 version, cos (being text file) it's easiest to
read :). I'll learn my lesson next time and expand the 2008 one.

> but SQL:2008 has
>
> <update statement: searched> ::=
>   UPDATE <target table> [ [ AS ] <correlation name> ]
>       SET <set clause list>
>       [ WHERE <search condition> ]
[snip]
> 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.

I stand (comprehensively :) ) corrected, thanks for clarifying.

Geoff

"postgres_sure"<postgres_sure@163.com> writes:
> I found "Do not include the table's name in the specification of a target column
>                  — for example, UPDATE tab SET tab.col = 1 is invalid." in the documentation. 

> Some people usually like to update table by alias. They want to add this feature.

Sorry, but we aren't ever going to accept such a patch, because it would
introduce an unavoidable ambiguity: is "SET a.b = " meant to be an
assignment to column b of table a, or is it meant to be an assignment to
sub-field b of composite column a?

Yeah, we could invent some resolution rules to deal with that, but
better to just not add the nonstandard syntax (and it IS nonstandard,
SQL:2011 has nothing about it) in the first place.
        regards, tom lane