Thread: 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
likewhere 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)
;

> -----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