sql - UPDATE 的 FROM 端如何与 UPDATE 的目标表相关？

Question

以下查询(来自此处Postgres SQL SELECT and UPDATE behaving differently)

update fromemailaddress
    set call = true 
    from email
    where email.fromemailaddress = fromemailaddress.fromemailaddress and
          LOWER(email.subject) ~ 'tester';

我的阅读方式是:

Line 1: update fromemailaddress

-- 我们告诉数据库我们正在更新 fromemailaddress 表

Line 2:        set call = true 

-- 我们告诉数据库名为“call”的字段将被设置为 true

Line 3:        from email
Line 4:         where email.fromemailaddress = fromemailaddress.fromemailaddress and
Line 5:              LOWER(email.subject) ~ 'tester';

-- 好的，现在事情变得模糊了。这里到底发生了什么？看起来数据库以某种方式获取了第 3、4 和 5 行中的查询结果，但这如何告诉它要更新 fromemailaddress 表中的哪些行？什么是伪代码？是不是像这样:

for each row in (query from lines 3, 4, 5)
    set call=true?

我只是看不出 SQL 更新的 FROM 端与另一端有何关系。

更新:

点击下面@Erwin 的回答中的有值(value)链接，我可以找到这些信息，这些信息触及了我试图理解的核心内容:

http://www.postgresql.org/docs/current/interactive/sql-update.html

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.

Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join.

Answer 1

您显示的UPDATE 查询与:

UPDATE fromemailaddress f
SET    call = true 
FROM  (
   SELECT fromemailaddress
   FROM   email 
   WHERE  subject ILIKE '%tester%'
   ) e
WHERE  e.fromemailaddress = f.fromemailaddress;

subject ILIKE '%tester%' 是 subject ~ 'tester' 的更快等价物。 LIKE、ILIKE 和正则表达式匹配 (~) 的详细信息 in the manual或在 dba.SE 上的相关回答中:

• Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

并且有效地与:

UPDATE fromemailaddress f
SET    call = true
WHERE  EXISTS (
   SELECT 1
   FROM   email e
   WHERE  e.fromemailaddress = f.fromemailaddress
   AND    e.subject ILIKE '%tester%'
   );

改用它。

如果表 email 中应该有多个行具有相同的 fromemailaddress 匹配 fromemailaddress 中的一行，那么这个表单只执行 一个每行更新，不像你不幸的原始。

不要对 fromemailaddress 在这里用作列和表名这一事实感到困惑。

仔细阅读说明书here和 here .特别是这一点:

from_list

A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).