Happydevdays Happydevdays - 7 months ago 12
SQL Question

how to insert bulk data based on bulk select

I have the following tables:

table1



rpid | fname | lname | tu | fu | tu_id | start_time

table2



tu_id | tu | fu | start_time

I want to populate table1's tu, fu and tu_id using the matching records in table2. I match them based on a time stamp.

UPDATE table1
INNER JOIN table1
ON date_trunc('hour', table1.start_time) date_trunc('hour', table2.start_time) AND table1.rpid=table2.tu
SET table1.tu_id= table2.tu_id, table1.fu = table2.fu, table1.tu=table2.tu;


I'm getting a syntax error right now like this:

ERROR: syntax error at or near "INNER"
LINE 1: UPDATE table1 INNER JOIN table1 on date_trunc('hour', table1.s...

I've never tried something like this before, so I'm sure I'm missing something obvious.
Any suggestions?

I know that the INNER JOIN itself is correct because I tried it first in a SELECT statement. It returns the data I want... Now I just to update the fields in table1.
thanks.

EDIT 1

Also just tried this:

UPDATE table1
SET tu_id, fu, tu FROM (
SELECT table2.tu_id, table2.fu, table2.tu
FROM table1, INNER JOIN table1 on date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time) AND table1.rpid=table2.tu
);


That gives me the syntax error:

ERROR: syntax error at or near ","
LINE 1: UPDATE table1 SET tu_id, fu, tu FROM ( SELECT table2.t...

Answer
UPDATE table1 SET tu_id= table2.tu_id, fu = table2.fu, tu=table2.tu
from table2
where 
    date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time) and table1.rpid=table2.tu 
;
Comments