Re: request for sql3 compliance for the update command

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hannu Krosing <hannu(at)tm(dot)ee>
Cc: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Justin Clift <justin(at)postgresql(dot)org>, Dave Cramer <dave(at)fastcrypt(dot)com>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: request for sql3 compliance for the update command
Date: 2003-02-20 14:58:05
Message-ID: 22060.1045753085@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Bruce Momjian kirjutas N, 20.02.2003 kell 06:16:
>> However, what solution do we have for UPDATE (coll...) = (select val...)
>> for folks? It is awkward to repeat a query multiple times in an UPDATE.

> hannu=# update target set
> hannu-# a = source.a1, b=source.a2, c=source.a3
> hannu-# from (select 1 as a1, 2 as a2, 3 as a3 ) as source
> hannu-# where id = 1
> hannu-# ;

I've been trying to think of a case that can't be handled by transposing
the sub-select into FROM. I'm not sure there are any. I thought for a
minute that grouped aggregates would be an issue. For example, suppose
table "totals" has one row for each distinct value of "groupid"
appearing in table "details", and you use it to store group aggregate
values. You can do

UPDATE totals SET
xmax = (SELECT max(x) FROM details WHERE groupid = totals.groupid),
xmin = (SELECT min(x) FROM details WHERE groupid = totals.groupid),
ymax = (SELECT max(y) FROM details WHERE groupid = totals.groupid),
ymin = (SELECT min(y) FROM details WHERE groupid = totals.groupid),
...

but that is awfully tedious and will be inefficiently implemented. This
is what Bruce is worried about. On the other hand, one could argue that
this is a wrongheaded way to go about it anyway, and the correct way is

UPDATE totals SET
xmax = ss.xmax, xmin = ss.xmin, ...
FROM
(SELECT groupid, max(x) AS xmax, ... FROM details GROUP BY groupid) ss
WHERE groupid = ss.groupid;

If there is indeed a row in "totals" for every groupid, then this will
certainly beat out the first approach that has to run a separate query
for each groupid, even if we avoid a separate query for each aggregate.
(It could maybe lose if you only wanted to update the totals for a few
groupids; but even then you could probably push the WHERE conditions
restricting the groups into the sub-select.)

Of course this syntax isn't standard either ... but we already have it.

Right now I'm not convinced there is a functionality argument for
supporting the Informix-style syntax, even with multiple columns.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-02-20 15:09:21 Re: deleting dependencies
Previous Message Tom Lane 2003-02-20 14:31:21 Re: request for sql3 compliance for the update command