Updating based on a join

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?

Responses

Browse pgsql-general by date

  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