PostgreSQL更新特定记录失败：替换table1中dog的ID为fish的ID

表结构与需求说明 #

现有两张PostgreSQL表结构及数据如下：

-- table1数据
A | table2_id | C
1 | 2         | 3
4 | 5         | 6
7 | 5         | 8

-- table2数据
ID | name
2  | 'cat'
5  | 'dog'
9  | 'fish'

需求：将table1中table2_id对应dog的记录，替换为fish对应的ID（即9），最终table1中table2_id为5的行需更新为9。

你的SQL错误原因 #

你编写的UPDATE语句：

UPDATE table1
SET table2_id = table2_updated.id
FROM table2 AS table2_updated
WHERE table1.table2_id = table2_updated.id AND table2_updated.name = 'fish';

核心逻辑错误：

• 该语句关联的是table2中name='fish'的行（ID=9），并要求table1.table2_id = 9才执行更新，但原table1中不存在table2_id=9的记录，因此没有任何行能匹配WHERE条件，导致无更新生效。

正确的解决方案 #

方案1：直接指定ID值（高效简洁） #

若已知dog的ID为5、fish的ID为9，直接执行：

UPDATE table1
SET table2_id = 9
WHERE table2_id = 5;

方案2：通过关联查询动态获取ID（适配ID可能变化的场景） #

如果不想硬编码ID，可以通过子查询或多表关联实现：

子查询写法

UPDATE table1
SET table2_id = (SELECT id FROM table2 WHERE name = 'fish')
WHERE table2_id = (SELECT id FROM table2 WHERE name = 'dog');

多表关联写法

UPDATE table1
SET table2_id = fish_table.id
FROM table2 AS dog_table
JOIN table2 AS fish_table ON fish_table.name = 'fish'
WHERE table1.table2_id = dog_table.id AND dog_table.name = 'dog';

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