如何对有序的sql列进行批量更新？

Question

我正在使用PostgreSQL，并有一个名为table_all的表，其中包含500万行和以下结构：

id|source_id|destination_id|some_flag
1 | 12      |  13          |  NULL
2 | 12      |  14          |  NULL
...

另一个表table_flag具有相同的行数和以下结构：

id|some_flag
1 | true
2 | false
...

我需要table_all table_flag的值设置some_flag。如果我发出这样的声明：

UPDATE table_all set some_flag = table_flag.some_flag 
from table_flag 
where table_all.id = table_flag.id

这需要几个小时，而且我需要经常做这个手术。这两个表按id排序，id是惟一的，并且这两个表都有id。对我来说，用< code>table_flag中的值设置< code>table_all中的< code>some_flag只是执行从< code>table_flag到< code>table_all的批量复制。有办法做到这一点吗？

Answer 1

防止实际上不会改变任何东西但同样昂贵的“空更新”，因为Postgres无论如何都需要插入新的行版本。

UPDATE table_all a
SET    some_flag = f.some_flag 
FROM   table_flag f
WHERE  a.id = f.id
AND    a.some_flag IS DISTINCT FROM f.some_flag;

对于可以为NULL的列，使用< code >与不同。< br >对于定义为NOT NULL的列< code >

此外，在数据库表中没有“顺序”,你有一个基本的误解。索引会有很大帮助，但是当您必须处理所有行时就没有用了。

您可能应该更改您的工作流程，以便能够选择要更新的几行。您当前的方法效率极低，即使使用了改进的查询。

Answer 2

如果标志确实是布尔列，我建议使用默认值和子查询的组合，例如：

UPDATE table_all SET some_flag = True WHERE id IN (SELECT id FROM table_flag WHERE some_flag=True)

编辑：删除语句“table_all上的（id，some_flag）上的非聚集索引可能会有所帮助。”因为正如Andrew所指出的，索引布尔列不是一个好主意。

Answer 3

如果您的更新是增量的（例如，主表中的大多数标志都不为空），请使用以下查询：

update table_all 
set some_flag = table_flag.some_flag 
from table_flag 
where 
    table_all.id = table_flag.id
    and (table_all.some_flag isnull
        or table_all.some_flag <> table_flag.some_flag);

如果主表中的所有（或主要部分）标志都为 null，则可以尝试使用游标：

do $$
declare  
    cur cursor for select id, some_flag from table_flag;
    rec record;
begin
    for rec in cur loop
        update table_all 
        set some_flag = rec.some_flag 
        where 
            table_all.id = rec.id
            and (table_all.some_flag isnull
                or table_all.some_flag <> rec.some_flag);
    end loop;
end $$;