Re: [PATCHES] extension for sql update

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Am Mittwoch, 26. Juli 2006 22:58 schrieb Tom Lane:
>> The reason people want this syntax is that they expect to be
>> able to write, say,
>> UPDATE mytab SET (foo, bar, baz) =
>> (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);

> I don't find any derivation in the standard that would permit this.

Well, there are two ways to get there. SQL99 does not actually have the
syntax with parentheses on the left, but what it does have is SET ROW:

<set clause> ::=
<update target> <equals operator> <update source>
| <mutated set clause> <equals operator> <update source>

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

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

and you can derive (SELECT ...) from <value expression> via

<value expression> ::=
...
| <row value expression>

<row value expression> ::=
...
| <row value constructor>

<row value constructor> ::=
...
| <row subquery>

<row subquery> ::= <subquery>

<subquery> ::=
<left paren> <query expression> <right paren>

<query expression> ::=
[ <with clause> ] <query expression body>

<query expression body> ::=
<non-join query expression>

<non-join query expression> ::=
<non-join query term>

<non-join query term> ::=
<non-join query primary>

<non-join query primary> ::=
<simple table>

<simple table> ::=
<query specification>

<query specification> ::=
SELECT [ <set quantifier> ] <select list>
<table expression>

Another interesting restriction in SQL99 is

9) If an <update target> specifies ROW, then:

a) <set clause list> shall consist of exactly one <set clause>
SC.

SQL2003 seems to have dropped the ROW syntax entirely, but instead they
have

<set clause> ::= <multiple column assignment>

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

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

and from there it goes through just like before.

regards, tom lane