Re: How to update an entire table by getting the values from another table? - Mailing list pgsql-novice

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