sql - 使用包含空值列的 WHERE 子句更新语句

Question

我正在使用另一个表中的数据更新一个表中的列。 WHERE 子句基于多列，其中一些列为空。根据我的想法，这个空值是 丢弃 你的标准 UPDATE TABLE SET X=Y WHERE A=B 语句。

参见 this SQL Fiddle我尝试根据 table_two 中的数据更新 table_one 的两个表中的一个。我的查询目前看起来像这样:

UPDATE table_one SET table_one.x = table_two.y 
FROM table_two
WHERE 
table_one.invoice_number = table_two.invoice_number AND
table_one.submitted_by = table_two.submitted_by AND
table_one.passport_number = table_two.passport_number AND
table_one.driving_license_number = table_two.driving_license_number AND
table_one.national_id_number = table_two.national_id_number AND
table_one.tax_pin_identification_number = table_two.tax_pin_identification_number AND
table_one.vat_number = table_two.vat_number AND
table_one.ggcg_number = table_two.ggcg_number AND
table_one.national_association_number = table_two.national_association_number

当任一表中的任何列都为 null 时，table_one.x 中的某些行查询失败，不会更新。也就是说，只有当所有列都有一些数据时，它才会更新。

这个问题与我之前的问题有关here on SO我使用 Distinct On 从大型数据集中获取不同的值。我现在想要的是用具有唯一字段的表中的值填充大型数据集。

更新

我使用了@binotenary 提供的第一个更新语句。对于小表，它会瞬间运行。示例有一个包含 20,000 条记录的表，并且更新在大约 20 秒内完成。但是到目前为止，另一个有 900 万条记录的表已经运行了 20 个小时!。请参阅下面的 EXPLAIN 函数

的输出

Update on table_one  (cost=0.00..210634237338.87 rows=13615011125 width=1996)
  ->  Nested Loop  (cost=0.00..210634237338.87 rows=13615011125 width=1996)
    Join Filter: ((((my_update_statement_here))))
    ->  Seq Scan on table_one  (cost=0.00..610872.62 rows=9661262 width=1986)
    ->  Seq Scan on table_two  (cost=0.00..6051.98 rows=299998 width=148)

EXPLAIN ANALYZE 选项也用了很长时间，所以我取消了它。

关于如何加快此类更新的任何想法？即使这意味着使用不同的更新语句，甚至使用自定义函数循环执行更新。

Answer 1

由于 null = null 的计算结果为 false，除了相等性检查之外，您还需要检查两个字段是否均为 null:

UPDATE table_one SET table_one.x = table_two.y 
FROM table_two
WHERE 
    (table_one.invoice_number = table_two.invoice_number 
        OR (table_one.invoice_number is null AND table_two.invoice_number is null))
    AND
    (table_one.submitted_by = table_two.submitted_by 
        OR (table_one.submitted_by is null AND table_two.submitted_by is null))
    AND 
    -- etc

您还可以使用 coalesce更具可读性的函数:

UPDATE table_one SET table_one.x = table_two.y 
FROM table_two
WHERE 
    coalesce(table_one.invoice_number, '') = coalesce(table_two.invoice_number, '')
    AND coalesce(table_one.submitted_by, '') = coalesce(table_two.submitted_by, '')
    AND -- etc

但是您需要注意默认值(coalesce 的最后一个参数)。
它的数据类型应与列类型相匹配(例如，这样您就不会最终将日期与数字进行比较)并且默认值应该不会出现在数据中
例如 coalesce(null, 1) = coalesce(1, 1) 是您希望避免的情况。

更新(关于性能):

对 table_two 进行序列扫描 - 这表明您在 table_two 上没有任何索引。
因此，如果您更新 table_one 中的一行，然后要在 table_two 中找到匹配的行，数据库基本上必须逐行扫描所有行，直到找到匹配项。< br/>如果相关列被索引，则可以更快地找到匹配的行。

另一方面，如果 table_one 有任何索引，则会减慢更新速度。
根据this performance guide :

Table constraints and indexes heavily delay every write. If possible, you should drop all the indexes, triggers and foreign keys while the update runs and recreate them at the end.

来自同一指南的另一个可能有用的建议是:

If you can segment your data using, for example, sequential IDs, you can update rows incrementally in batches.

例如，如果 table_one 是一个 id 列，您可以添加如下内容

and table_one.id between x and y

到 where 条件并多次运行查询，更改 x 和 y 的值，以便覆盖所有行。

The EXPLAIN ANALYZE option took also forever

在处理带有副作用的语句时，在将 ANALYZE 选项与 EXPLAIN 一起使用时，您可能需要小心。根据documentation :

Keep in mind that the statement is actually executed when the ANALYZE option is used. Although EXPLAIN will discard any output that a SELECT would return, other side effects of the statement will happen as usual.