postgresql - Updating database columns based on returned results from a SQL Select statement

Question

I have a simple table, which is queried from my backend every minute.

id (int) | phone_number (string) | start (timedatestamp) | period (string) | occurances (int)

I make an sql query, which runs every minute, and returns the results. It's selects all phone_numbers which start this minute.

SELECT * FROM table 

WHERE start >= date_trunc('minute', now()) and
      start < date_trunc('minute', now()) + interval '1 minute'

as results

This runs fine, but I need to update the table as well, based on this select results.

There are two parts to this:

1. For each selected row, I need the occurrences to decrement by 1 and update the database with this
2. For each selected row, if the periodicity='MONTHLY", I need the start column to change to the date and time exactly a month from now.

Is it possible to do this in one SQL statement? Any help or examples are greatly appreciated :)

question from:https://stackoverflow.com/questions/65904296/updating-database-columns-based-on-returned-results-from-a-sql-select-statement

Answer 1

Yes and you can do so directly. The only 'twist' is when a column in mentioned in the SET clause Postgres always writes the Rvalue. When you desire to conditionally update a column you set the Rvalue to the existing value when the condition is not meet. See fiddle here.

update atable 
   set occurances = occurances-1 
     , start_tm = case when period_txt = 'Monthly' 
                       then now()+interval '1 month'
                       else start_tm
                  end
 where date_trunc('minute',start_tm) = date_trunc('minute',now());