Title: how to insert bulk data based on bulk select
Tags: sql;postgresql;sql-update
Question: 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...

Here is the accepted 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

Here is another answer: I believe this is the easiest way:
UPDATE table1 t
SET tu_id = t2.tu_id,
fu = t2.fu,
tu = t2.tu
FROM  table2 t2
WHERE date_trunc('hour', t.start_time) = date_trunc('hour', t2.start_time)
AND   t.rpid=t2.tu;

Here is another answer: You cannot update multiple columns with only one select before postgres 9.5 (the last stable version)
So the syntax will be something like :
Before 9.5:
UPDATE table1
SET  tu_id = (SELECT table2.tu_id
FROM table1
INNER JOIN table2
ON date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time)
AND table1.rpid=table2.tu),
fu = (SELECT table2.fu
FROM table1
INNER JOIN table2
ON date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time)
AND table1.rpid=table2.tu),
tu = (SELECT table2.tu
FROM table1
INNER JOIN table2
ON date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time)
AND table1.rpid=table2.tu);
And 9.5+ :
UPDATE table1
SET  (tu_id, fu, tu) = (
SELECT table2.tu_id,  table2.fu, table2.tu
FROM table1
INNER JOIN table2
ON date_trunc('hour', table1.start_time) = date_trunc('hour', table2.start_time)
AND table1.rpid=table2.tu
Edit :
I forgot the UPDATE ... FROM ..., thanks to other repliers for refreshing that in my memory !
Comment for this answer: It's because your SELECT returns more than one row. Try with a LIMIT 1 and it will work (but it probably won't do what you want)
Comment for this answer: I tried the 9.5 example but i'm getting an error that says : ERROR:  more than one row returned by a subquery used as an expression

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 3, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2016:00:04/27/16', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><postgresql><sql-update>', 'Title': 'how to insert bulk data based on bulk select', 'ViewCount': 63, 'id': 36898115, 'user_AboutMe': None, 'user_AccountId': 5616117.0, 'user_DisplayName': 'Happydevdays', 'user_Reputation': 1902.0, 'user_Views': 123.0, 'user_id': 4447395.0}}