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?
