CODEWITHSUNDEEP
sqlpostgresql
user3262353
user3262353

Reputation: 107

Update one table from another doing some math

Postgres 9.6.6, latest Ubuntu LTS.

I have a big main table and a small one, who receiving data from external sensors. Each record of the small one have the record id of the big one. 

Table_1                       Table_2
  id  temp0  temp0temp1         temp1   Tab1_Id
   1    3       0                 35        2
   2    5       0                 15        3
   3    8       0                 75        1
   4    9       0                 45        4
   5    3       0                   .some
   6    8       0
   7    2       0
   .tens of thousand...

I'am looking for an efficient solution to update each record of the big one, doing some math, ie: 

Table 1 (after)
  id  temp0  temp0temp1
   1    3      78
   2    5      40
   3    8      23
   4    9      54
   5    3       0
   6    8       0
   7    2       0

Something similar to: 

UPDATE Table_1 
SET temp0temp1 = Table_1.temp0 + (SELECT temp1 
                                  FROM Table_2 
                                  WHERE table_2.Tab1_Id = Table_1.Id)...

Thanks
Perez

Upvotes: 0

Views: 45

Answers (3)

Dejan Dozet
Dejan Dozet

Reputation: 1009

update t1
set temp0temp1 = temp0 + temp1
from Table_1 t1 join
  Table_2 t2 on t1.id = t2.Tab1_Id

Upvotes: 0

Rohit
Rohit

Reputation: 2152

You can create a trigger. On each insert of table 1 run a procedure that will update table 2 with desired calculations.

Upvotes: 0

wargre
wargre

Reputation: 4753

You can use a update ... from

UPDATE Table_1 t1
  SET temp0temp1 = t1.temp0 + t2.temp1
  from Table_2 t2 
  WHERE t2.Tab1_Id = t1.Id

Upvotes: 1

Related Questions