sql - 在另一个表中的一行的 UPDATE 中使用同一个表中的多行

Question

我正在为 PostgreSQL (9.1) 使用 pgadmin，我的查询运行时间太长

update tableA a
set owner1_surname = (select owner_surname from owners_distinct b where a.owner1= b.owner),
owner1_othername   = (select owner_othername from owners_distinct b where a.owner1= b.owner),
owner2_surname     = (select owner_surname from owners_distinct b where a.owner2= b.owner),
owner2_othername   = (select owner_othername from owners_distinct b where a.owner2= b.owner),
owner3_surname     = (select owner_surname from owners_distinct b where a.owner3= b.owner),
owner3_othername   = (select owner_othername from owners_distinct b where a.owner3= b.owner)

不必一次又一次地从owners_distinct 表 中检索值，是否可以检索列owner、owner_surname 和owner_othername 只使用 SELECT 一次，然后根据检查在 tableA 的列上执行 UPDATE？

Answer 1

这比我最初想象的要棘手，因为您想多次连接同一个表，而唯一的连接是更新后的表本身:

UPDATE table_a a
    SET owner1_surname = b1.owner_surname
    ,owner1_othername  = b1.owner_othername
    ,owner2_surname    = b2.owner_surname
    ,owner2_othername  = b2.owner_othername
    ,owner3_surname    = b3.owner_surname
    ,owner3_othername  = b3.owner_othername
FROM   table_a x
LEFT   JOIN owners_distinct b1 ON b1.b.owner = x.owner1
LEFT   JOIN owners_distinct b2 ON b2.b.owner = x.owner2 
LEFT   JOIN owners_distinct b2 ON b3.b.owner = x.owner3
WHERE  x.table_a_id = a.table_a_id

其中table_a_id是table_a的主键。通常您不必再次加入表，但在这种情况下，您需要它来加入 before 您可以链接到更新的表。

我使用 LEFT JOIN，以防止在 owners_distinct 中找不到三个所有者之一时，一行的整个更新失败。

数据库设计

你确定你需要 table_a 中的所有冗余数据吗？规范化架构中的规范方式是仅存储外键(owner1、owner2、owner3) ，并使用 SELECT 中的 JOIN 按需获取姓名的详细信息。完全删除所有要更新的列。当然，规则总是有异常(exception)......

没有唯一键？

一开始就不应该发生这种情况。您应该添加一个代理主键，例如:

ALTER TABLE table_a ADD table_a_id serial PRIMARY KEY;

相关答案中的更多信息:
Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?

没有唯一键的解决方案

无论如何，无论任何独特的列如何，这里都有一种方法可以进行此更新:

UPDATE table_a a
    SET owner1_surname = b1.owner_surname
    ,owner1_othername  = b1.owner_othername
    ,owner2_surname    = b2.owner_surname
    ,owner2_othername  = b2.owner_othername
    ,owner3_surname    = b3.owner_surname
    ,owner3_othername  = b3.owner_othername
FROM   (SELECT DISTINCT owner1, owner2, owner3 FROM table_a) x
LEFT   JOIN owners_distinct b1 ON b1.b.owner = x.owner1
LEFT   JOIN owners_distinct b2 ON b2.b.owner = x.owner2 
LEFT   JOIN owners_distinct b2 ON b3.b.owner = x.owner3
WHERE  x.owner1 = a.owner1
AND    x.owner2 = a.owner2
AND    x.owner3 = a.owner3;

重点是:我们只需要 (owner1, owner2, owner3) 的每个组合一次。