Re: [HACKERS] extension for sql update

On Sun, Jul 30, 2006 at 08:38:30PM -0400, Rod Taylor wrote:
> On Sun, 2006-07-30 at 20:20 -0400, Robert Treat wrote:
> > On Thursday 27 July 2006 09:28, Bruce Momjian wrote:
> > > Tom Lane wrote:
> > > > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > > > Tom Lane wrote:
> > > > >> UPDATE mytab SET (foo, bar, baz) =
> > > > >> (SELECT alpha, beta, gamma FROM othertab WHERE key = mytab.key);
> > > > >
> > > > > That UPDATE example is interesting because I remember when using
> > > > > Informix that I had to do a separate SELECT statement for each UPDATE
> > > > > column I wanted to update. I didn't realize that you could group
> > > > > columns and assign them from a single select --- clearly that is a
> > > > > powerful syntax we should support some day.
> > > >
> > > > No question. The decision at hand is whether we want to look like
> > > > we support it, when we don't yet. I'd vote not, because I think the
> > > > main use-case for the row-on-the-left syntax is exactly this, and
> > > > so I fear people will just get frustrated if they see it in the
> > > > syntax synopsis and try to use it.
> > >
> >
> > I'm not a big fan of implementing partial solutions (remember "left-joins are
> > not implemented messages" :-) way back when) , however in my experience with
> > this form of the update command, the primary usage is not to use a subselect
> > to derive the values, but to make it easier to generate sql, using a single
>
> I disagree. UPDATE mytab SET (foo, bar, baz) =(SELECT ...) is the
> specifications way of doing an update with a join. That is its primary
> purpose.
>
> UPDATE ... FROM is a PostgreSQL alternative to the above.

An alternative that people have been using without complaint for years
(probably because a number of other databases do the same thing).

Perhaps a good compromise would be to allow UPDATE ... (SELECT) where it
would meet the current requirements for UPDATE ... FROM.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461