Re: Updating based on a join

Patrick Nelson <pnelson@neatech.com> writes:
> My update is:

> UPDATE db1
>  SET dat=(SELECT MIN(dat) FROM db2 WHERE id='A')
>  WHERE id='A';

> Which does exactly one record in db1 and I would like them all done.  So I
> used the FROM clause like:

> UPDATE db1
>  SET dat=c.dat
>  FROM (SELECT DISTINCT ON (b.id) a.sym, b.dat FROM db1 a, db2 b WHERE
> a.id=b.id) AS c
>  WHERE db1.id=c.id;

Seems messy.  Why not

UPDATE db1
 SET dat = (SELECT MIN(b.dat) FROM db2 b WHERE db1.id = b.id);

I think this is actually SQL-standard, as well as being more readable;
the form with FROM is definitely not standard.

            regards, tom lane