使用WHERE子句更新语句，该子句包含具有空值的列

一尘不染

使用WHERE子句更新语句，该子句包含具有空值的列

sql

我正在使用另一个表中的数据更新一个表上的列。该WHERE子句基于多个列，并且某些列为空。根据我的想法，此空值是throwingoff您的标准UPDATE TABLE SET X=Y WHERE A=B声明。

请参阅两个表中的该SQLFiddle，这些表正在尝试table_one根据中的数据进行更新table_two。我的查询当前如下所示：

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

当某些table_one.x表中的任何列为时，由于某些行未更新，查询失败null。即，仅当所有列都有一些数据时，它才会更新。

更新

我使用了@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选项也花了很长时间，所以我取消了。

关于如何使这种类型的更新更快的任何想法？即使这意味着使用其他更新语句，甚至使用自定义函数来循环执行更新。

阅读 175

2021-05-16

共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)您要避免这种情况。

更新（关于性能）：

Seq Scan on table_two-这表示您在上没有任何索引table_two。
因此，如果您在其中更新一行，table_one则要在table_two数据库中找到匹配的行，基本上必须逐一扫描所有行，直到找到匹配的行。
如果对相关列进行索引，则可以更快找到匹配的行。

另一方面，如果table_one有任何索引，则会减慢更新速度。

表约束和索引严重延迟了每次写操作。如果可能，应在更新运行时删除所有索引，触发器和外键，并在最后重新创建它们。

同一指南中另一个可能有用的建议是：

如果您可以使用例如顺序ID对数据进行细分，则可以批量更新行。

因此，例如，如果您要添加table_one一id列，则可以添加如下内容

and table_one.id between x and y

在where条件和运行查询几次改变的值，x并且y使所有的行覆盖。

EXPLAIN ANALYZE选项也花了很长时间

在处理带有副作用的语句时，使用该ANALYZE选项EXPLAIN时可能要小心。根据文件：

请记住，使用ANALYZE选项时，该语句实际上已执行。尽管EXPLAIN会丢弃SELECT将返回的任何输出，但是该语句的其他副作用将照常发生。

2021-05-16