Re: request for sql3 compliance for the update command

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