CODEWITHSUNDEEP
sqlpostgresqlsql-update
Happydevdays
Happydevdays

Reputation: 2072

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

Upvotes: 0

Views: 93

Answers (3)

firetonton
firetonton

Reputation: 323

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 !

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

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;

Upvotes: 0

cur4so
cur4so

Reputation: 1820

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 
;

Upvotes: 1

Related Questions