Re: request for sql3 compliance for the update command

With no one replying on how to do correlated subqueries in FROM for
UPDATE, I am adding this to the TODO list:

* Allow UPDATE tab SET ROW (col, ...) = (...) for updating
multiple columns

Several people indicated they wanted this functionality. The ROW is SQL
standard, and the column list is an extension.

I do not see any way to allow subqueries without requiring two levels of
parentheses, one for the list, another for the subquery. ROW should
also be optional.

---------------------------------------------------------------------------

Bruce Momjian wrote:
>
> While I can see a subquery in UPDATE as working in most cases:
>
> UPDATE tab
> SET col - t.col
> FROM (SELECT col from xx) AS t
> WHERE ...
>
> but I don't see that working for correlated subqueries, where you want
> to set a column based on a value you are updating. (Many use correlated
> subqueries in UPDATE a lot.) Do FROM subqueries work as correlated
> subqueries? I can't see how they would because you don't have a row
> being processed at the FROM stage of the query.
>
> I did look at the SQL99 standards and ROW does appear there:
>
> <update statement: positioned> ::=
> UPDATE <target table>
> SET <set clause list>
> WHERE CURRENT OF <cursor name>
>
> <set clause list> ::=
> <set clause> [ { <comma> <set clause> }... ]
>
> <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>
>
> and later it says:
>
> a) If <update target> specifies ROW, then let CL be the set of
> all columns of T.
>
> The TODO item would be:
>
> Support SQL99 UPDATE SET ROW = () with extension SET ROW (col ...) = ()
>
> This also gets into that weird Informix syntax where you have to
> double-paren when you want to use a subquery. Basically, this thing
> keeps getting wierder and wierder.
>
> ---------------------------------------------------------------------------
>
> Dave Cramer wrote:
> > Given that the direction of the spec seems to be headed towards the
> > desired syntax, can we put this on the TODO list?
> >
> > Dave
> >
> > On Thu, 2003-02-20 at 11:49, Dave Cramer wrote:
> > > Scott,
> > >
> > > I can't find page 858 in that document, is it the right one?
> > >
> > > also the link s/b ?
> > >
> > > ftp://ftp.sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf
> > >
> > > Dave
> > > On Thu, 2003-02-20 at 11:20, scott.marlowe wrote:
> > > > On Thu, 20 Feb 2003, Tom Lane wrote:
> > > >
> > > > > Hannu Krosing <hannu(at)tm(dot)ee> writes:
> > > > > > Are you against it just on grounds of cleanliness and ANSI compliance,
> > > > > > or do you see more serious problems in letting it in ?
> > > > >
> > > > > At this point it seems there are two different things being tossed
> > > > > about. I originally understood Dave to be asking for parens to be
> > > > > allowed around individual target column names, which seems a useless
> > > > > frammish to me. What Bruce has pointed out is that a syntax that lets
> > > > > you assign multiple columns from a single rowsource would be an actual
> > > > > improvement in functionality, or at least in convenience and efficiency.
> > > > > (It would also be a substantial bit of work, which is why I think this
> > > > > isn't what Dave was offering a quick patch to do...) What I'd like to
> > > > > know right now is which interpretation Informix actually implements.
> > > > >
> > > > > I don't like adding nonstandard syntaxes that add no functionality ---
> > > > > but if Informix has done what Bruce is talking about, that's a different
> > > > > matter altogether.
> > > >
> > > > Tom, I was purusing the wild and wonderfully exciting new SQL
> > > >
> > > > (found here:
> > > > ftp://sqlstandards.org/SC32/WG3/Progression_Documents/FCD/4FCD1-01-Framework-2002-01.pdf)
> > > >
> > > > ANSI TC NCITS H2
> > > > ISO/IEC JTC 1/SC 32/WG 3
> > > > Database
> > > >
> > > > document to see what it had to say, and on this subject, and it looks like
> > > > update is going to be supporing this same style we're discussing here.
> > > >
> > > > Look on or around p. 858 in that doc.)
> > --
> > Dave Cramer <dave(at)fastcrypt(dot)com>
> > Cramer Consulting
> >
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073