postgre为什么 SQL UPDATE on JOIN 会更新每个条目，但 SELECT 会选择正确的条目

Question

我正在尝试基于另一个表更新一个表。以下SQL在我期望它更新myTableB.active = true的位置时更新myTableA中的每个条目：

UPDATE
myTableA
SET
myTableA.enabled = false
FROM
myTableA MTA
FULL OUTER JOIN
myTableB MTB
ON
MTA.user_id = MTB.user_id
WHERE
MTB.active = true

上面的代码更新 MTA 中的 1000 个条目，无论MTB.active的状态如何。但是，如果将前 4 行替换为SELECT *，则按预期，它仅选择 500 行。

如何仅更新MTAMTB.active = true中的条目？

Answer 1

编写此查询的正确方法是：

UPDATE myTableA MTA
SET myTableA.enabled = false
FROM myTableB MTB
WHERE MTA.user_id = MTB.user_id AND MTB.active = true;

UPDATE和FROM中对myTableA的引用是不同的引用。 因此，您的查询正在执行CROSS JOIN。 在SELECT中，这相当于：

FROM MyTableA update_A CROSS JOIN
(myTableA MTA FULL JOIN
myTableB MTB
ON MTA.user_id = MTB.user_id
)
WHERE MTB.active = true

显然，筛选器与要更新的表无关。

还有两点。 首先，FULL JOIN不太可能用于UPDATE。 通常，行需要匹配才能进行任何更新。 此外，= true是多余的。active显然是一个布尔列，因此它可以用作完整的布尔表达式。

Answer 2

一个相关的子查询在这里可能就足够了：

update mytableA
set enabled = false
where exists (
select 1
from mytableB mtb
where mtb.user_id = mytableA.user_id and mtb.active = true
)

Answer 3

您的查询应如下所示：

UPDATE myTableA MTA
SET myTableA.enabled = false
FROM myTableB MTB
WHERE MTA.user_id = MTB.user_id AND MTB.active = true