Update a table use result of another query in Postgres

Question

I have a 2 table like this:

table1: (time, value, id) , table2:(time, value, id, ...)

I need to update table1 with a result of a query on table2 based on id, for example the query can be:

SELECT * from table2 where value > 2

and this query returns more than hundreds of rows,

I need update table1 with these rows based on id (set time=q.time, value=q.value where id=q.id) is it possible with sql query?

I don't need UPSERT as I'm sure I have same id in both tables, just need update

please provide sample data and desired output

eshirvana
– eshirvana

2021-04-16 18:46:49 +00:00
Commented Apr 16, 2021 at 18:46 — eshirvana
– eshirvana, Commented Apr 16, 2021 at 18:46

user330315 · Accepted Answer · 2021-04-16 18:59:00Z

4

Postgres supports a FROM clause for UPDATE:

update table1
   set time = q.time, 
       value = q.value
from table2 q
where table2.id = table1.id
  and table2.value > 2;

answered Apr 16, 2021 at 18:59

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

arilwan Over a year ago

simple and precise.

biu · Accepted Answer · 2022-09-21 18:55:15Z

0

SELECT * 
into temp buffer     
from table2 
where value > 2;


insert into table1(list of columns to be updated)
select (list of columns to be selected)
from buffer;

NOTE___ --buffer is a temporary table that stores the output of the main query -- make sure both tables (table1 and table2) have the same columns and are arranged in the same order

edited Sep 21, 2022 at 18:55

answered Sep 21, 2022 at 18:53

biu

11 bronze badge

Collectives™ on Stack Overflow

Update a table use result of another query in Postgres

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related