How to INSERT INTO one table from another table, WHERE

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Kirk Wythers
Sent: Friday, May 03, 2013 1:51 PM
To: POSTGRES
Subject: [GENERAL] How to INSERT INTO one table from another table,
WHERE

I am trying to insert data from 2 columns in tableB (colX and colY)
into the same two columns of tableB, with a join like where clause. Is
this possible?

For example:

INSERT INTO tableA (colX, colY)
(SELECT colX, colY
FROM tableB
WHERE
tableA.blockname = tableB.block_name
AND tableA.timestamp = tableB.timestamp) ;

If it's not the whole record but just some columns, you UPDATE them not INSERT:

UPDATE tableA A
SET colX = B.colx, colY = B.colY
FROM table B B
WHERE A. blockname = B.block_name
AND A.timestamp = B.timestamp;

Note the use of aliases (A, B).

b.t.w. "timestamp" - isn't a good choice for column name, being a data type it's on the list of reserved words.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general