我知道有人问过这个问题,但我无法得到结果。我希望以下答案类似的问题。我有2个表表1:
CREATE TABLE table1 (session_id INT, timestamp INT, track DOUBLE PRECISION);
INSERT INTO table1 (session_id) VALUES (106502),(137226),
(114701),(124942),(155663)
表二:
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
的另外2列为空,我想从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
这在dbfiddle中进行了说明。
1条答案
按热度按时间xyhw6mcr1#
您似乎试图用与
session_id
匹配的table2
对应行覆盖table1
中的空白行,添加其余行并将不匹配的空白保留在原处。Online demo