Postgres SQL UPDATE based on result of a previous SELECT

Question

In Postgres 10 I want to perform an UPDATE twice. First UPDATE should run no matter what, updating the alwaysupdate column. And the second UPDATE should run only if the SELECT statement below returns a row count of 0, which means sometimesupdate should get updated only if all of the rows in mytable where mykey = 100 have sometimesupdate set to 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

What's the most efficient way to do this? Is it possible to combine it into a single SQL statement? Otherwise multiple statements wrapped in a transaction? Otherwise a function if necessary.

Gordon Linoff · Accepted Answer · 2018-01-24 23:48:56Z

1

One method puts the logic in the from clause:

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;

However, not exists would usually have better performance:

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;

answered Jan 24, 2018 at 23:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user779159 Over a year ago

In your second example doesn't case need an else like in the first example or would it work without? When you set sometimesupdate = sometimesupdate, does Postgres know not to update the table since nothing is actually changing, or do modification triggers etc still run in that case?

Gordon Linoff Over a year ago

@user779159 . . . No. The value will be set to NULL without the else -- but because of the logic of the query, we know the value is already NULL. I will grant that the query might be clearer with an ELSE clause.

RoMEoMusTDiE · Accepted Answer · 2018-01-24 22:46:05Z

1

Try this

if count is equal to zero then update to now() otherwise retain the old value of 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

or if you want to update that specific row which has no sometimesupdate and is keyb = 200 then do below

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

edited Jan 24, 2018 at 22:46

answered Jan 24, 2018 at 22:34

RoMEoMusTDiE

4,8341 gold badge19 silver badges26 bronze badges

Comments

joop · Accepted Answer · 2018-01-25 11:32:07Z

You can use a chained CTE, and make the second update conditional on an EXISTS(...) [NOT EXISTS() is the same as COUNT()==zero]

  -- 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
        );

Collectives™ on Stack Overflow

Postgres SQL UPDATE based on result of a previous SELECT

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related