UPDATE 的 FROM 端与 UPDATE 的目标表有何关系? [英] How does the FROM side of an UPDATE relate to the table targeted for UPDATE?

问题描述

以下查询(源自此处Postgres SQL SELECT 和 UPDATE 行为不同)

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

我的阅读方式是:

Line 1: update fromemailaddress

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

-- we tell the database that we are updating the fromemailaddress table

Line 2:        set call = true 

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

-- we tell the database that the field named "call" will be set to true

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

-- 好的，现在事情变得模糊了.这里实际发生了什么?似乎数据库以某种方式获取了第 3 行、第 4 行和第 5 行中的查询结果，但这如何告诉它要更新 fromemailaddress 表中的哪些行?什么是伪代码?是不是类似:

-- OK now things are getting fuzzy. What actually happens here? It appears that somehow the database takes the result of the query in lines 3 4 and 5, but how does that tell it which rows to update in the fromemailaddress table? What is the pseudocode? Is it something like:

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

我只是看不到 SQL 更新的 FROM 端与另一端的关系.

I just can't see how the FROM side of the SQL update relates to the other side.

更新:

按照下面@Erwin 回答中的宝贵链接，我可以找到这些信息，这些信息是我试图理解的核心:

Following the valuable links in @Erwin's answer below lead me to this information that gets to the core of what I was trying to understand:

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

当出现 FROM 子句时，本质上发生的是目标表连接到 from_list 中提到的表，并且连接的每个输出行代表一个更新操作目标表.使用 FROM 时，应确保连接产生对于要修改的每一行，最多一个输出行.换句话说，一个目标行不应与另一行连接多于一行表.如果是这样，那么只有一个连接行将用于更新目标行，但使用哪一行并不容易可预测的.

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.

推荐答案

你显示的 UPDATE 查询完全一样:

The UPDATE query you display is exactly the same as:

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 和正则表达式匹配 (~) 的详细信息 在手册中 或在 dba.SE 上的相关答案中:

subject ILIKE '%tester%' is a faster equivalent for subject ~ 'tester'. Details for LIKE, ILIKE and regular expression matching (~) in the manual or in this related answer on dba.SE:

• PostgreSQL 中与 LIKE、SIMILAR TO 或正则表达式的模式匹配

和有效一样:

And effectively the same as:

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 中的一行，则此表单仅执行 一个每行更新，不像你不幸的原始版本.

If there should be multiple rows in table email with the same fromemailaddress matching a row in fromemailaddress, then this form only executes one update per row, unlike your unfortunate original.

不要被 fromemailaddress 在这里用作列和表名这一事实混淆.

Don't be confused by the fact that fromemailaddress is used as column and as table name here.

仔细阅读手册这里和这里.特别是这一点:

Read the manual carefully here and here. In particular this bit:

from_list

表表达式列表，允许来自其他表的列出现在 WHERE 条件和更新表达式中.这是类似于可以在 中指定的表列表FROM 子句SELECT 语句.注意目标表不能出现在from_list，除非您打算自加入(在这种情况下，它必须在 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).

这篇关于UPDATE 的 FROM 端与 UPDATE 的目标表有何关系?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！