Re: request for sql3 compliance for the update command - Mailing list pgsql-hackers
From | Bruce Momjian |
---|---|
Subject | Re: request for sql3 compliance for the update command |
Date | |
Msg-id | 200303171849.h2HIns907836@candle.pha.pa.us Whole thread Raw |
In response to | Re: request for sql3 compliance for the update command (Bruce Momjian <pgman@candle.pha.pa.us>) |
Responses |
Re: request for sql3 compliance for the update command
(Hannu Krosing <hannu@tm.ee>)
|
List | pgsql-hackers |
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@tm.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@fastcrypt.com> > > Cramer Consulting > > > > > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.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@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
pgsql-hackers by date: