

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: 20 February 2003 14:31
> To: Hannu Krosing
> Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers
> Subject: Re: [HACKERS] request for sql3 compliance for the 
> update command 
> 
> 
> 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.

Informix SE allows me to do:

CREATE TABLE djp(col1 INTEGER, col2 INTEGER)
INSERT INTO djp VALUES(1, 2)
UPDATE djp SET(col1, col2) = (3, 4)

However

UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp)

Results in a syntax error. I don't have Informix IDS so I don't know if
that can do it.

Regards, Dave.

Thu, 20 Feb 2003 16:05:57 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: request for sql3 compliance for the update command 
Informix supports 2 different styles for the update - your one would have to 
be written :


UPDATE djp SET(col1, col2) = ((SELECT col1,col2 FROM some_other_table))

Notice the double brackets !
The first signifies a list of values - the second is the brackets around the 
subquery...

(NB If you try to reference the same table in the Update - you'll get an 
error....)


For single columns you could still write :

UPDATE djp SET col1 = (SELECT col2 FROM some_other_table)

Notice - one more set of brackets on the right as on the left....


> UPDATE djp SET(col1, col2) = (SELECT col2, col1 FROM djp)


Thu, 20 Feb 2003 19:09:54 +0000
Mike Aubury <mike@aubit.com>
Re: request for sql3 compliance for the update command