sql - Postgres UPDATE x WHERE id IN y

Question

以前可能有人问过这个问题，但是用谷歌搜索“IN”之类的关键字效果不是很好。

这是我的查询:

UPDATE tblCustomer SET type = 2 
WHERE idcustomer                                
IN (SELECT fidcustomer1
    FROM tblorder                   
     UNION                      
    SELECT fidcustomer2
    FROM tblorder                   
   )                                

将其分解:对于出现在订单表中任一列中的所有客户，我想将所有客户的类型(只是一个 int)设置为 2。

在我的测试数据上，这些表中没有一个包含超过几百行，但查询运行了很多分钟(即使没有 UNION，这似乎也没有太大区别)，显然是重新做客户每行内部查询一次。显然，我可以将其重写为单个 SELECT DISTINCT(id)，然后进行数百个单行更新，并使用我用于 ODBC 访问的任何语言来执行逻辑，但这只是一个 hack。

我怎样才能正确重写它？

附录:我要更新的表包含许多相对较大的 BYTEA blob，每行几 MB。它们被设置为外部存储或扩展，但我想知道这是否会使顺序扫描变慢。所有的更新似乎都需要很长时间，不仅仅是这一次。

Answer 1

我建议一个更简单的方法:

UPDATE tblCustomer c
SET    type = 2 
FROM   tblorder o
WHERE  c.idcustomer IN (o.fidcustomer1, o.fidcustomer2)
AND    c.type IS DISTINCT FROM 2  -- optional, to avoid empty updates

除非，如果 tblorder 中有重复项，那么与您所拥有的类似的方法是明智的:

UPDATE tblCustomer c
SET    type = 2 
FROM  (
    SELECT fidcustomer1 AS cust FROM tblorder
    UNION
    SELECT fidcustomer2 FROM tblorder
    ) o
WHERE  c.idcustomer = o.cust
AND    c.type IS DISTINCT FROM 2;

无论哪种方式，在 PostgreSQL 中，定期连接表比 IN 表达式执行得更好。