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

I am updating a column on one table using data from another table. The WHERE clause is based on multiple columns and some of the columns are null. From my thinking, this nulls are what are throwing off your standard UPDATE TABLE SET X=Y WHERE A=B statement.

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

See this SQL Fiddle of the two tables where am trying to update table_one based on data from table_two. My query currently looks like 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

The query fails for some rows in that table_one.x isn't getting updated when any of the columns in either table are null. i.e. it only gets updated when all columns have some data.

对于某些行的查询失败,因为当任一表中的任何列为空时,table_one.x都不会更新。即只有当所有列都有一些数据时才会更新。

This question is related to my earlier one here on SO where I was getting distinct values from a large data set using Distinct On. What I now I want is to populate the large data set with a value from the table which has unique fields.

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

UPDATE

I used the first update statement provided by @binotenary. For small tables, it runs in a flash. Example is had one table with 20,000 records and the update was completed in like 20 seconds. But another table with 9 million plus records has been running for 20 hrs so far!. See below the output for EXPLAIN function

我使用了@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)

The EXPLAIN ANALYZE option took also forever so I canceled it.

EXPLAIN ANALYZE选项也是永远的,所以我取消了它。

Any ideas on how to make this type of update faster? Even if it means using a different update statement or even using a custom function to loop through and do the update.

有关如何更快地进行此类更新的任何想法?即使它意味着使用不同的更新语句,甚至使用自定义函数循环并执行更新。

5 个解决方案

#1

Since null = null evaluates to false you need to check if two fields are both null in addition to equality check:

由于null = null的计算结果为false,因此除了进行相等性检查外,还需要检查两个字段是否都为空:

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

You could also use the coalesce function which is more readable:

您还可以使用更易读的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

But you need to be careful about the default values (last argument to coalesce).
It's data type should match the column type (so that you don't end up comparing dates with numbers for example) and the default should be such that it doesn't appear in the data
E.g coalesce(null, 1) = coalesce(1, 1) is a situation you'd want to avoid.

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

Update (regarding performance):

Seq Scan on table_two - this suggests that you don't have any indexes on table_two.
So if you update a row in table_one then to find a matching row in table_two the database basically has to scan through all the rows one by one until it finds a match.
The matching rows could be found much faster if the relevant columns were indexed.

在table_two上进行Seq Scan - 这表明table_two上没有任何索引。因此,如果更新table_one中的行,然后在table_two中找到匹配的行,则数据库必须逐个扫描所有行,直到找到匹配为止。如果相关列被索引,则可以更快地找到匹配的行。

On the flipside if table_one has any indexes then that slows down the update.
According to this performance guide:

另一方面,如果table_one有任何索引,则会降低更新速度。根据这个性能指南:

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.

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

Another suggestion from the same guide that might be helpful is:

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

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

如果您可以使用(例如)顺序ID对数据进行分段,则可以批量逐步更新行。

So for example if table_one an id column you could add something like

因此,例如,如果table_one是一个id列,你可以添加类似的东西

and table_one.id between x and y

to the where condition and run the query several times changing the values of x and y so that all rows are covered.

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

The EXPLAIN ANALYZE option took also forever

EXPLAIN ANALYZE选项也是永远的

You might want to be careful when using the ANALYZE option with EXPLAIN when dealing with statements with sideffects. According to documentation:

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

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.

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

#2

Try below, similar to the above @binoternary. Just beat me to the answer.

尝试下面,类似于上面的@binoternary。只是打败了我的答案。

update table_one
set column_x = (select column_y from table_two 
where 
(( table_two.invoice_number = table_one.invoice_number)OR (table_two.invoice_number IS NULL AND table_one.invoice_number IS NULL))
and ((table_two.submitted_by=table_one.submitted_by)OR (table_two.submitted_by IS NULL AND table_one.submitted_by IS NULL)) 
and ((table_two.passport_number=table_one.passport_number)OR (table_two.passport_number IS NULL AND table_one.passport_number IS NULL)) 
and ((table_two.driving_license_number=table_one.driving_license_number)OR (table_two.driving_license_number IS NULL AND table_one.driving_license_number IS NULL)) 
and ((table_two.national_id_number=table_one.national_id_number)OR (table_two.national_id_number IS NULL AND table_one.national_id_number IS NULL)) 
and ((table_two.tax_pin_identification_number=table_one.tax_pin_identification_number)OR (table_two.tax_pin_identification_number IS NULL AND table_one.tax_pin_identification_number IS NULL)) 
and ((table_two.vat_number=table_one.vat_number)OR (table_two.vat_number IS NULL AND table_one.vat_number IS NULL)) 
and ((table_two.ggcg_number=table_one.ggcg_number)OR (table_two.ggcg_number IS NULL AND table_one.ggcg_number IS NULL)) 
and ((table_two.national_association_number=table_one.national_association_number)OR (table_two.national_association_number IS NULL AND table_one.national_association_number IS NULL)) 
);

#3

You can use a null check function like Oracle's NVL. For Postgres, you will have to use coalesce.

您可以使用Oracle的NVL之类的空检查功能。对于Postgres,你必须使用coalesce。

i.e. your query can look like :

即您的查询可能如下所示:

UPDATE table_one SET table_one.x =(select  table_two.y from table_one,table_two
WHERE 
coalesce(table_one.invoice_number,table_two.invoice_number,1) = coalesce(table_two.invoice_number,table_one.invoice_number,1) 
AND
coalesce(table_one.submitted_by,table_two.submitted_by,1) = coalesce(table_two.submitted_by,table_one.submitted_by,1))

where table_one.table_one_pk in  (select  table_one.table_one_pk from table_one,table_two
WHERE 
coalesce(table_one.invoice_number,table_two.invoice_number,1) = coalesce(table_two.invoice_number,table_one.invoice_number,1) 
AND
coalesce(table_one.submitted_by,table_two.submitted_by,1) = coalesce(table_two.submitted_by,table_one.submitted_by,1));

#4

Your current query joins two tables using Nested Loop, which means that the server processes

您当前的查询使用嵌套循环连接两个表,这意味着服务器进程

9,661,262 * 299,998 = 2,898,359,277,476

rows. No wonder it takes forever.

行。难怪它需要永远。

To make the join efficient you need an index on all joined columns. The problem is NULL values.

要使连接有效,您需要在所有连接列上使用索引。问题是NULL值。

If you use a function on the joined columns, generally the index can't be used.

如果在连接列上使用函数,通常不能使用索引。

If you use an expression like this in the JOIN:

如果在JOIN中使用这样的表达式:

coalesce(table_one.invoice_number, '') = coalesce(table_two.invoice_number, '')

an index can't be used.

索引不能使用。

So, we need an index and we need to do something with NULL values to make index usable.

所以,我们需要一个索引,我们需要用NULL值做一些事情来使索引可用。

---

We don't need to make any changes in table_one, because it has to be scanned in full in any case.

我们不需要在table_one中进行任何更改,因为在任何情况下都必须完整扫描。

But, table_two definitely can be improved. Either change the table itself, or create a separate (temporary) table. It has only 300K rows, so it should not be a problem.

但是,table_two绝对可以改进。要么更改表本身,要么创建单独的(临时)表。它只有300K行,所以它应该不是问题。

Make all columns that are used in the JOIN to be NOT NULL.

使JOIN中使用的所有列都为NOT NULL。

CREATE TABLE table_two (
    id int4 NOT NULL,
    invoice_number varchar(30) NOT NULL,
    submitted_by varchar(20) NOT NULL,
    passport_number varchar(30) NOT NULL,
    driving_license_number varchar(30) NOT NULL,
    national_id_number varchar(30) NOT NULL,
    tax_pin_identification_number varchar(30) NOT NULL,
    vat_number varchar(30) NOT NULL,
    ggcg_number varchar(30) NOT NULL,
    national_association_number varchar(30) NOT NULL,
    column_y int,
    CONSTRAINT table_two_pkey PRIMARY KEY (id)
);

Update the table and replace NULL values with '', or some other appropriate value.

更新表并将NULL值替换为''或其他适当的值。

Create an index on all columns that are used in JOIN plus column_y. column_y has to be included last in the index. I assume that your UPDATE is well-formed, so index should be unique.

在JOIN加column_y中使用的所有列上创建索引。 column_y必须包含在索引的最后。我假设您的UPDATE格式正确,因此索引应该是唯一的。

CREATE UNIQUE INDEX IX ON table_two
(
    invoice_number,
    submitted_by,
    passport_number,
    driving_license_number,
    national_id_number,
    tax_pin_identification_number,
    vat_number,
    ggcg_number,
    national_association_number,
    column_y
);

The query will become

查询将成为

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

Note, that COALESCE is used only on table_one columns.

请注意,COALESCE仅用于table_one列。

It is also a good idea to do UPDATE in batches, rather than the whole table at once. For example, pick a range of ids to update in a batch.

分批进行UPDATE也是一个好主意,而不是一次完成整个表。例如,选择要批量更新的一系列ID。

UPDATE table_one SET table_one.x = table_two.y 
FROM table_two
WHERE 
table_one.id >= <some_starting_value> AND
table_one.id < <some_ending_value> AND
COALESCE(table_one.invoice_number, '') = table_two.invoice_number AND
COALESCE(table_one.submitted_by, '') = table_two.submitted_by AND
COALESCE(table_one.passport_number, '') = table_two.passport_number AND
COALESCE(table_one.driving_license_number, '') = table_two.driving_license_number AND
COALESCE(table_one.national_id_number, '') = table_two.national_id_number AND
COALESCE(table_one.tax_pin_identification_number, '') = table_two.tax_pin_identification_number AND
COALESCE(table_one.vat_number, '') = table_two.vat_number AND
COALESCE(table_one.ggcg_number, '') = table_two.ggcg_number AND
COALESCE(table_one.national_association_number, '') = table_two.national_association_number

#5

You can use coalesce function which will return true every time when any variable passed is null. Null check function will help you.

您可以使用coalesce函数,每当传递的任何变量为null时,它将返回true。空检查功能将帮助您。

Null-related functions here.

这里与空相关的功能。