Re: How to update an entire table by getting the values from another table?

In response to A B :
> Hi. I need to run an update query on a table, and need some help on
> how to do it.
>
>
> I have a table A(id integer primary key, prod_id integer, Y1 integer,
> Y2 integer, ... Y14 integer) and a table B (a integer references A, x
> integer,unique(a) );
>
> and now I should set (for each row in A) prod_id to have the value
> that is found in table B
>
> How do I write a statement that does that?

test=# select * from a;
id | prod_id | y1
----+---------+----
1 | |
2 | |
3 | |
(3 rows)

test=*# select * from b;
a | x
---+----
1 | 10
2 | 20
3 | 30
(3 rows)

test=*# update a set prod_id = b.x from b where a.id=b.a;
UPDATE 3
test=*# select * from a;
id | prod_id | y1
----+---------+----
1 | 10 |
2 | 20 |
3 | 30 |
(3 rows)

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net