sql - 为什么当您引用具有别名的字段时 SQL UPDATE FROM 静默失败？

Question

昨天我发现了 SQL 的一些奇怪之处，或者至少 PostreSQL。请看下面并解释为什么第一个查询什么都不做，而第二个查询恰到好处。

-- this silently does nothing
update bodycontent 
    set body = replace(body, '~' || u.oldusername, '~' || u.newusername)
    from usermigration u;

-- this works as expected
update bodycontent 
    set body = replace(body, '~' || oldusername, '~' || newusername)
    from usermigration u;

更新:我认为每个人都忽略了这个问题的要点，笛卡尔积是一个初衷:将有 N x M 更新，这是设计使然。

我需要从 bodycontent 的每一行中替换迁移表中存在的所有用户名对。

而且，我再说一遍，第二个版本按预期工作，但第一个版本没有更新。我只想知道为什么。

| usermigration table    |
--------------------------
oldusername | newusersname
--------------------------
johndoe     | johnd
john.smith  | johnsmith

这是 PostgreSQL 中的错误吗？

Answer 1

您缺少一个 WHERE 子句(不能写成直接的 JOIN 条件)。附加表 usermigration 必须绑定(bind)到表 bodycontent 以某种方式更新，或者 bodycontent 的每一行都有尽可能多的更新候选者因为 usermigration 中有行 - 两个表之间的笛卡尔积。

无法判断哪一个将被应用和持久化。这两种说法在这方面都是错误的。例如，如果 usermigration 中有 1000 行，bodycontent 中有 1000 行，这将导致 1 000 000 更新候选项，然后再选择 1000 行.

如果您在 UPDATE 语句中加入一个或多个表，那么如果没有连接结果的 WHERE 子句，它几乎没有任何意义 FROM 子句更新表。

考虑 manual about the UPDATE statement 中的这些注释 :

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

请注意，UPDATE 语句中的 FROM 子句是 SQL 标准的 PostgreSQL 扩展。其他 DBMS 使用不同的语法，例如对要更新的表(在 tSQL 中)的显式 JOIN 不适用于 PostgreSQL。

---

回答评论中的附加问题

这个查询应该有效，大部分¹

UPDATE bodycontent b
SET    body = replace(b.body, u.oldusername, u.newusername)
FROM   usermigration u
WHERE  b.body LIKE ('%' || u.oldusername || '%');

¹ 结果仍然不明确。可以找到多个匹配项。不确定将应用哪一个。问题是您的要求本质上是模棱两可的。可以有多个(重叠的)用户名匹配，并且应用更新的顺序是相关的(但未定义)。 UPDATE 语句完美地反射(reflect)了您有缺陷的要求。

'~' || 部分怎么了？