Thread: Re: request for sql3 compliance for the update command

> -----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.

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)

Dave, Tom,

As a rabid SQL-compliance geek, I vote no.  The Infomix syntax is not standard
and does not add any functionality which is not already present with other
syntax.

However, this could make a nice start on an /contrib/informix/ package, if
Dave wanted to volunteer to be permanent maintainer ...

--
-Josh BerkusAglio Database SolutionsSan Francisco

> -----Original Message-----
> From: Mike Aubury [mailto:mike@aubit.com]
> Sent: 20 February 2003 19:10
> To: Dave Page; Tom Lane; Hannu Krosing
> Cc: Dave Cramer; Peter Eisentraut; Pgsql Hackers
> Subject: Re: [HACKERS] 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....)

Ahh, of course. I tried double brackets 'cos I figured I might need one
pair to indicate the set and one to indicate the subselect, but I didn't
think to try a different table.

Regards, Dave.