Re: request for sql3 compliance for the update command

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> UPDATE totals SET
> xmax = (SELECT max(x) 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;
...
> Of course this syntax isn't standard either ... but we already have it.

This is nice, but I could see it being a big pain if the join clause wasn't so
neat and tidy as a groupid column that you can group by. The Informix syntax
has some appeal -- speaking from the point of view of someone who has had to
write some awkward update statements like this in the past. (In Oracle where
the best syntax is to create an updatable inline view which is pretty much
equivalent in expressiveness to the Postgres syntax.)

Consider how awkward this query would be if the iterations in the original
query overlapped for example. You would have to introduce a another table to
the select just to drive the join artificially.

For example consider a hypothetical case:

UPDATE networks set num_hosts = (select count(*) from hosts where addr << netblock)

Where some hosts are on multiple nested netblocks.

The only way I see to convert that to Postgres's syntax would be to join
against the networks table again and then group by the primary key of the
networks table. Ick.

--
greg