如何将firsttable所有行替换为secondtable中对应ID的行？

数据表结构与初始数据 #

创建并初始化两个数据表的SQL如下：

create table firsttable (id int, speed double precision);
create table secondtable (id int, speed double precision);

insert into firsttable (id) 
values (1), (2);

insert into secondtable (id, speed)
values (1, 12.3), (1, 12.4), (2, 12.43), (2, 11), (2, 13.6), (3,23);

预期结果 #

希望最终firsttable的数据如下：

id  speed
1   12.3
1   12.4
2   12.43
2   11
2   13.6

尝试的错误语句 #

最初尝试的SQL出现语法错误：

insert into firsttable(speed)
select speed from secondtable
where id in firsttable.id

错误提示：

ERROR:  syntax error at or near "firsttable"
LINE 4: where id in firsttable.id

编辑补充 #

• 尝试Tushar提供的方法后，得到的结果包含不需要的null行：

id  speed
1   null
2   null
1   12.3
1   12.4
2   12.43
2   11
2   13.6

• 再次尝试的SQL及结果：

INSERT INTO firsttable (id, speed)
SELECT  id,speed 
FROM secondtable 
WHERE EXISTS (SELECT id FROM firsttable);

结果包含原null行和id=3的行，不符合预期：

id  speed
1   null
2   null
1   12.3
1   12.4
2   12.43
2   11
2   13.6
3   23

正确解决方案 #

你的需求核心是替换firsttable的数据，只保留secondtable中id与原firsttable匹配的行，可以通过以下步骤实现：

1. 先记录原firsttable中的id范围
2. 清空firsttable原有数据
3. 插入secondtable中符合id条件的数据

具体SQL如下：

-- 用CTE暂存原firsttable的id
WITH original_ids AS (
    SELECT DISTINCT id FROM firsttable
)
-- 删除firsttable原有数据
DELETE FROM firsttable;

-- 插入secondtable中id匹配的行
INSERT INTO firsttable (id, speed)
SELECT st.id, st.speed
FROM secondtable st
JOIN original_ids oi ON st.id = oi.id;

执行后，firsttable的数据将完全符合预期。

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