From: | Patrick Nelson <pnelson(at)neatech(dot)com> |
---|---|
To: | "PostgreSQL List (E-mail)" <pgsql-general(at)postgresql(dot)org> |
Subject: | Updating based on a join |
Date: | 2002-10-22 06:01:50 |
Message-ID: | 4165C48DE9A0D211B6400800095C585F172E7F@WASHINGTON |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-10-22 06:22:58 | Re: Updating based on a join |
Previous Message | Joe Conway | 2002-10-22 05:02:42 | Re: example of Create Function |