Thread: Updating based on a join

db1 (one)
 id varchar(5)
 dat varchar(8)

db2 (many)
 id varchar(5)
 dat varchar(8)

SELECT a.id,a.dat,b.id,b.dat FROM db1 a, db2 b WHERE a.id=b.id; shows
something like

|  id  |  dat  |  dat  |
-------+-------+--------
 A     |  293  |  277  |
 A     |  293  |  284  |
 A     |  293  |  293  |
...

I would like to update a.dat with the first (numerically 277) b.dat so that
the same select query would be:

|  id  |  dat  |  dat  |
-------+-------+--------
 A     |  277  |  277  |
 A     |  277  |  284  |
 A     |  277  |  293  |
...


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;

Which seemed to have worked, but I'm not sure I've covered always getting
the min value of multiple db2.dat for a given db1.id although my sub seems
to have produced that ends I'm not confident.  Any comments?  Anecdotes?

On Mon, 21 Oct 2002, Patrick Nelson wrote:

> db1 (one)
>  id varchar(5)
>  dat varchar(8)
>
> db2 (many)
>  id varchar(5)
>  dat varchar(8)
>
> 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;
>
> Which seemed to have worked, but I'm not sure I've covered always getting
> the min value of multiple db2.dat for a given db1.id although my sub seems
> to have produced that ends I'm not confident.  Any comments?  Anecdotes?

I believe you need an order by in order to guarantee the minimum value.
order by b.id, b.dat would probably do it.

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