Updating based on a join
От | Patrick Nelson |
---|---|
Тема | Updating based on a join |
Дата | |
Msg-id | 4165C48DE9A0D211B6400800095C585F172E7F@WASHINGTON обсуждение исходный текст |
Ответы |
Re: Updating based on a join
Re: Updating based on a join |
Список | pgsql-general |
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?
В списке pgsql-general по дате отправления: