含INNER JOIN的UPDATE语句未限制更新行数的原因及改写方法

我想要下面这条带INNER JOIN的SQL UPDATE语句只更新匹配条件的有限行数，而非全表：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;

原本预期它和下面这些带WHERE子句的语句效果一致：

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2
WHERE Join_Test_1.PKID = Join_Test_2.PKID;

UPDATE Join_Test_1
SET ANumber = 3
WHERE EXISTS (SELECT * FROM Join_Test_2 
              WHERE Join_Test_2.PKID = Join_Test_1.PKID);

UPDATE Join_Test_1
SET ANumber = 3
WHERE PKID IN (SELECT PKID FROM Join_Test_2);

但实际执行时，带WHERE子句的语句只会更新PKID匹配的行，而那条带INNER JOIN的语句却更新了表中所有行。

为什么INNER JOIN无法限制更新行数？ #

问题出在你在FROM子句里重复引用了要更新的表Join_Test_1，且未将更新目标表与JOIN后的结果集做关联匹配。

在PostgreSQL的UPDATE语法中，当你在FROM子句中包含目标表的别名（这里是ST1），但没在WHERE子句或JOIN条件里把Join_Test_1（更新目标）和ST1关联时，数据库会把目标表与FROM子句的结果集做笛卡尔积：目标表的每一行都会和ST1与ST2的JOIN结果进行无关联匹配，只要FROM子句的结果集不为空，目标表所有行都会被更新。

简单来说，你相当于让数据库把Join_Test_1的每一行，和ST1 JOIN ST2的结果做无关联匹配，然后更新每一行的ANumber为3——只要ST1 JOIN ST2有至少一行结果，全表都会被更新。

如何改写带INNER JOIN的语句，使其限制更新行数？ #

有两种正确的改写方式：

方式1：去掉目标表在FROM里的重复引用，直接关联Join_Test_2 #

UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2 AS ST2
WHERE Join_Test_1.PKID = ST2.PKID;

这种写法通过WHERE子句把更新目标表和FROM里的Join_Test_2关联起来，只更新匹配的行，和你给出的第二个正确语句本质一致。

方式2：用JOIN直接关联目标表和Join_Test_2，避免重复引用 #

UPDATE ST1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;

这种写法直接把要更新的表（ST1）作为FROM子句的一部分，通过INNER JOIN筛选出需要更新的行，数据库会只更新ST1中与ST2匹配的行。

完整示例验证 #

/* 扩展示例 */
CREATE TABLE Join_Test_1 (PKID SERIAL, ANumber INTEGER);
CREATE TABLE Join_Test_2 (PKID SERIAL, ANumber INTEGER);

INSERT INTO Join_Test_1 (ANumber) VALUES (1), (1);      
INSERT INTO Join_Test_2 (ANumber) VALUES (2);       

-- 错误写法：更新全表（2行）
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID; 

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;
-- 结果：
-- 1, 3
-- 2, 3

-- 重置数据
UPDATE Join_Test_1 
SET ANumber = 1; 

-- 正确改写方式1：只更新匹配行（1行）
UPDATE Join_Test_1
SET ANumber = 3
FROM Join_Test_2 AS ST2
WHERE Join_Test_1.PKID = ST2.PKID;

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;
-- 结果：
-- 1, 1
-- 2, 3     

-- 重置数据
UPDATE Join_Test_1 SET ANumber = 1; 

-- 正确改写方式2：只更新匹配行（1行）
UPDATE ST1
SET ANumber = 3
FROM Join_Test_1 AS ST1
INNER JOIN Join_Test_2 AS ST2 ON ST1.PKID = ST2.PKID;

SELECT * 
FROM Join_Test_1 
ORDER BY PKID;
-- 结果：
-- 1, 1
-- 2, 3     

DROP TABLE IF EXISTS Join_Test_1;
DROP TABLE IF EXISTS Join_Test_2;

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