sql - 奇怪的 SQL 更新查询

Question

我有一个查询，但我不确定如何写。我有两个具有相同结构的表:

Table1 (id, value1, value2, value_of_interest)

Table2 (id, value1, value2, value_of_interest)

我想在 Table2 上找到所有行，其中 value1 和 value2 匹配，并且 Table1.value_of_interest 为空，并从 Table2 复制这些值。所以它的 SQL 是:

UPDATE Table1
SET Table1.value_of_interest = Table2.value_of_interest
FROM Table2
WHERE Table1.value_of_interest == ''
AND Table1.value1 == Table2.value1
AND Table1.value2 == Table2.value2

但是，也有可能 Table2 上的匹配行集没有唯一的 value_of_interest。如果 Table2 中的匹配行集具有唯一的 value_of_interest，我只想更新 Table1.value_of_interest。

例如，假设我们有

Table1(0, 1, 1, '')
Table2(0, 1, 1, 1)
Table2(1, 1, 1, 1)

然后我会将 Table1.value_of_interest 更新为 1。但是如果我有:

Table1(0, 1, 1, '')
Table2(0, 1, 1, 1)
Table2(0, 1, 1, 2)

那我什么都不做。我在想一些类似于 GROUP BY 的东西，带有聚合函数并测试 DISTINCT 值是否等于 1，但我无法弄清楚。任何建议将不胜感激。

附言。我正在使用 PostgreSQL 和 SQLAlchemy，但我无法使用 ORM 执行此操作，因此我尝试执行直接的 SQL 查询。

Answer 1

最简单的方法是添加一个 NOT EXISTS半反加入:

UPDATE table1 t1
SET    value_of_interest = t2a.value_of_interest
FROM  (
   SELECT DISTINCT value1, value2, value_of_interest
   FROM   table2
   ) t2a
WHERE  t1.value_of_interest = ''
AND    t2a.value_of_interest IS DISTINCT FROM ''
AND    t1.value1 = t2a.value1
AND    t1.value2 = t2a.value2
AND NOT EXISTS (
   SELECT 1
   FROM   table2 t2b
   WHERE  t2b.value1 = t2.value1
   AND    t2b.value2 = t2.value2
   AND    t2b.value_of_interest <> t2a.value_of_interest
   );

还有:

• SET 子句中没有针对目标列的表限定。

  SET <strike>Table1.</strike>value_of_interest = ...

  我引用 the manual here :

  Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

• SQL 中没有==，只有=。
• t2.value_of_interest IS DISTINCT FROM '' .. 以避免空的更新(不改变任何内容以获得全部成本)。
• 为了避免重复的多次冗余更新，我将普通表 t2a 转换为折叠它们的子查询。