使用UPDATE从table2更新table1未得多行结果，求正确实现方法

我知道类似问题有现成解答，但按解答操作后没得到预期结果。现有两张表：

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)

Table1的timestamp和track列目前为空，我尝试用以下SQL从Table2更新这些列：

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

预期结果 #

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

解决方案 #

UPDATE语句的作用是修改现有行，每个session_id在Table1中只有一行，因此即使关联到Table2的多行数据，最终只会保留最后匹配的那一行的值（不同数据库可能取任意一行，但结果都是单条）。要实现多行结果，需要用INSERT ... SELECT语句插入新行：

方法：插入Table2对应数据到Table1 #

直接将Table2中与Table1存在匹配session_id的记录插入到Table1，同时保留原Table1中无匹配的空行：

INSERT INTO table1 (session_id, timestamp, track)
SELECT t2.session_id, t2.seconds, t2.track
FROM table2 t2
JOIN table1 t1 ON t2.session_id = t1.session_id;

如果需要先清除Table1中已关联的旧数据（避免重复行），可以先执行删除：

DELETE FROM table1
WHERE session_id IN (SELECT DISTINCT session_id FROM table2);

再执行上面的插入语句即可得到预期结果。

内容的提问来源于stack exchange，提问作者arilwan