在不存在行时从另一个表中更新

Question

我试图运行以下查询：

UPDATE tempd tvl
    SET id = case 
                when tse.id is null or tse.id = 0 then --coalesce(nullif(tse.id,0),5) 
                 5  
                else  
                    tse.event_id  end  
FROM details tse 
WHERE
    tvl.id = tse.id
AND tvl.name = tse.name
AND tvl.add =  tse.add;

表tempd的样本

name  add id
a     xx
b     yy
c     zz
d     ss

details表现在为空。因此，tempd中的每一行都应该有5，但是我总是在id列中得到0。

另外，我们可以从多个表中更新吗？例如:如果有来自table1的匹配，那么使用它，如果匹配来自table2，则使用默认值。

Answer 1

WHERE子句中的谓词排除的行是，而不是在所有中更新。这是查询的核心问题。

您希望无条件地更新表的所有行表tempd。赋值中的相关子查询执行任务--不使用外部WHERE子句：

UPDATE tempd tvl
SET    id = COALESCE((
                     SELECT tse.event_id
                     FROM   details tse 
                     WHERE  tse.id   = tvl.id
                     AND    tse.name = tvl.name
                     AND    tse.add  = tvl.add
                     AND    tse.id   <> 0  -- excludes NULL & 0
                  -- ORDER  BY ???
                  -- LIMIT  1  -- can there be > 1 match?
                     ), 5);

我们可以从多个表中更新吗？例如:如果有来自table1的匹配，那么使用它，如果匹配来自table2，则使用默认值。

单程：

UPDATE tempd tvl
SET    id = COALESCE(NULLIF(
                     (
                     SELECT tse.event_id
                     FROM   details tse 
                     WHERE  tse.id   = tvl.id
                     AND    tse.name = tvl.name
                     AND    tse.add  = tvl.add
                     -- AND    tse.id   <> 0
                     -- this time we need to accept 0 to exit
                     -- ORDER  BY ??? 
                     -- LIMIT  1  -- can there be > 1 match?
                     )   
                   , (
                     SELECT t2.some_id
                     FROM   tbl2 t2
                     WHERE  ...  -- your filters here

                     ), 0), 5);

所有括号都是必需的。

如果每个相关子查询中有超过一个匹配，LIMIT 1将修复这个问题。但是，通常情况下，您需要在每个ORDER BY之前添加LIMIT 1以获得一个确定性的选择。

请参见：

• Nullify column in update if subquery returns empty
• PostgreSQL update join vs SQL Server update join

Answer 2

重要的是where子句是什么，或者您要求的更新是什么。如果从空表进行更新，则始终会得到零行更新。

还有一个更简单的例子：

sophia=> create table empty_table(x integer);
CREATE TABLE
sophia=> create table foo(x integer);
CREATE TABLE
sophia=> insert into foo(x) values (1),(2),(3);
INSERT 0 3
sophia=> update foo set x = 5 from empty_table where true;
UPDATE 0
sophia=> 

至于解决方案--您可以根据具体的用例从以下其中之一进行选择：

• 从子查询获得要更新的值(如Erwin的示例所示)，
• 做一些事情，以便细节总是有数据、

、

• 、更新，所以源总是有某种东西(例如，与其他东西的连接)