Skip site navigation (1) Skip section navigation (2)

Re: request for sql3 compliance for the update command

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Dave Cramer <dave(at)fastcrypt(dot)com>
Cc: scott(dot)marlowe(at)ihs(dot)com, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: request for sql3 compliance for the update command
Date: 2003-03-05 21:52:44
Message-ID: 200303052152.h25LqiD13922@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-03-05 21:53:20
Subject: Re: [PATCHES] Non-colliding auto generated names
Previous:From: Tom LaneDate: 2003-03-05 21:36:13
Subject: Re: PGTTY?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group