Re: Updating based on a join

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.