代码之家 › 专栏 › 技术社区 › user779159

Postgres SQL更新基于先前选择的结果

sql-update postgresql sql

user779159 · 技术社区 · 7 年前

在Postgres 10中,我想执行两次更新。无论如何,第一次更新都应该运行,更新 alwaysupdate 柱只有当下面的SELECT语句返回的行计数为0时,才应该运行第二次更新,这意味着 sometimesupdate 仅当中的所有行 mytable 哪里 mykey = 100 有 有时更新 设置为 null .

-- Run no matter what, updating 'alwaysupdate'
update mytable set alwaysupdate = now() where keyA = 100 and keyB = 200

-- Check the number of rows where 'sometimesupdate' has a value
select count(*) from mytable where keyB = 200 and sometimesupdate is not null

-- If 'sometimesupdate' is null for all rows above, give it a value in this row
update mytable set sometimesupdate = now() where keyA = 100 and keyB = 200

最有效的方法是什么?是否可以将其合并到一个SQL语句中?否则,在一个事务中包装多个语句?否则,如有必要,请使用函数。

3 回复 | 直到 7 年前

Gordon Linoff 7 年前

一种方法将逻辑置于 from 条款:

update mytable
    set alwaysupdate = now(),
        sometimesupdate = (case when b.cnt = 0 then now() else sometimesupdate end)
    from (select count(*) from mytable where keyB = 200 and sometimesupdate is not null
         ) b
    where keyA = 100 and keyB = 200;

然而 not exists 通常会有更好的性能:

update mytable
    set alwaysupdate = now(),
        sometimesupdate = (case when not exists (select 1 from mytable where keyB = 200 and sometimesupdate is not null)
                                then now()
                           end)
         ) b
    where keyA = 100 and keyB = 200;

RoMEoMusTDiE 7 年前

试试这个

如果count等于零,则更新为now(),否则保留旧值sometimesupdate

update mytable as A
 set alwaysupdate = now(),
 sometimesupdate = (case when (
       select count(*) from mytable as B where B.keyB = A.keyB 
       and sometimesupdate is not null) = 0 
    then now() 
  else sometimesupdate end)
where keyA = 100 and keyB = 200

或者,如果您想更新没有sometimesupdate且keyb=200的特定行,请执行以下操作

UPDATE mytable
SET alwaysupdate = now(),
    sometimesupdate = (CASE
      WHEN keyB = 200 THEN CASE
          WHEN sometimesupdate IS NULL THEN now()
          ELSE sometimesupdate
        END
      ELSE sometimesupdate
    END)
WHERE keyA = 100
AND keyB = 200

joop 7 年前

您可以使用链式CTE,并使第二次更新以 EXISTS(...) [不存在()与COUNT()相同==零]

  -- Run no matter what, updating 'alwaysupdate'
WITH u1 AS ( 
        UPDATE mytable 
        SET alwaysupdate = now() 
        WHERE keyA = 100 AND keyB = 200;
        RETURNING *
        )
UPDATE mytable u2
SET sometimesupdate = now() 
FROM u1
WHERE u1.keyA = u2.keyA -- 100
  AND u1.keyB = u2.keyB -- 200
        -- If 'sometimesupdate' is null for all rows below, give it a value in this row
        -- Check if there are any rows where 'sometimesupdate' has a value
AND NOT EXISTS (SELECT * 
        FROM mytable nx
        WHERE nx.keyB = u2.keyB -- 200
        AND sometimesupdate IS NOT NULL
        );