CODEWITHSUNDEEP
sqlpostgresql
arilwan
arilwan

Reputation: 3993

update table column with select from another table

I know this question has been asked but I cannot manage to get the results I want following answers to similar question. I have 2 tables Table1:

CREATE TABLE table1 (session_id INT, timestamp INT, track DOUBLE PRECISION);

INSERT INTO table1 (session_id) VALUES (106502),(137226),
  (114701),(124942),(155663)

Table2:

CREATE TABLE table2 (session_id INT, seconds INT, lat DOUBLE PRECISION,
  lon DOUBLE PRECISION, track DOUBLE PRECISION);

INSERT INTO table2 (session_id, seconds, lat, lon, track)
VALUES ( 106502, 1462559236, 41.1726876, -8.5985753,150),
     (106502, 1462559237, 41.1726365, -8.5985595, 155),
     (106502, 1462559238, 41.1725735, -8.5985308,156),
     (106502, 1462559239, 41.1725079, -8.5984963, 156),
     (106502, 1462559240, 41.1724459, -8.5984539, 154),
     (137226, 1513974852, 41.1078345, -8.6268529, 194),
     (137226, 1513974853, 41.1077562,-8.6268664, 184),
     (137226, 1513974854, 41.1076747,-8.6268582, 173),
     (114701, 1467878080, 41.1654988, -8.6027799, 69),
     (114701, 1467878081, 41.1655208, -8.6027348, 49)

The 2 other columns of table1 are empty, and I want update it from table2.

So I issued:

UPDATE table1 t1
   SET timestamp = t2.seconds, 
       track = t2.track
from table2 t2
where t1.session_id = t2.session_id

session_id | timestamp | track
-----------+-----------+------
124942     |           |
155663     |           |
106502     |1462559236 | 150
114701     |1467878081 | 49
137226     |1513974854 | 173

Required results:

session_id | timestamp | track
-----------+-----------+------
124942     |           |
155663     |           |
106502     |1462559236 | 150
106502     |1462559237 | 155
106502     |1462559238 | 156
106502     |1462559239 | 156
106502     |1462559240 | 154
137226     |1513974852 | 194
137226     |1513974853 | 184
137226     |1513974854 | 173
114701     |1467878080 |  69
114701     |1467878081 |  49

This is illustrated in dbfiddle.

Upvotes: 0

Views: 52

Answers (1)

Zegarek
Zegarek

Reputation: 26467

You seem to be trying to overwrite blank rows in table1 with their table2 counterparts matched by session_id, adding the rest and leaving the unmatched blanks in place.

delete from table1 
where session_id in (select session_id from table2);

insert into table1
      (session_id, timestamp, track) 
select session_id, seconds,   track 
from table2;

Online demo

Upvotes: 1

Related Questions