奇怪的SQL UPDATE查询

ericso 发表于 Dev

埃里索

我有一个查询，我不确定该怎么写。我有两个具有相同结构的表：

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。我只想更新Table1.value_of_interest匹配行的集合Table2是否具有唯一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的值，但我无法弄清楚。任何意见，将不胜感激。

PS。我使用的是PostgreSQL和SQLAlchemy，但无法使用ORM进行此操作，因此我尝试进行直接的SQL查询。

欧文·布兰德斯特

最简单的方法是添加一个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 
      
      
       
       Table1.
      
      value_of_interest = ...
```
我在这里引用手册：

不要在目标列的规范中包括表的名称，例如，UPDATE tab SET tab.col = 1无效。
==在SQL中没有，只有=。
t2.value_of_interest IS DISTINCT FROM '' ..避免空的UPDATE（不更改全部费用）。
为了避免重复出现多个冗余更新，我将普通表转换为t2a折叠这些子查询的子查询。