How to reuse a newly assign column in a PostgreSQL update sentence

Question

From this simple example it is clear that a newly assigned value of a column in an update sentence cannot simply be reused in other columns assignment.

drop table if exists stocks cascade;

create table stocks (
    id              serial,
    stock_available int,
    stock_locked    int
);

insert into stocks(stock_available, stock_locked) values 
(100, 10),
(100, 10),
(100, 10),
(100, 10),
(100, 10);

update stocks
set stock_available = 5, stock_locked = stock_available + 1;

select * from stocks;

id|stock_available|stock_locked|
--|---------------|------------|
 1|              5|         101|
 2|              5|         101|
 3|              5|         101|
 4|              5|         101|
 5|              5|         101|

What I am wondering if there is something like excluded for updates.

score 1 · Accepted Answer · answered Aug 29 '20 at 17:35

There is no "exclude" clause in the update statement. There is however an "include" clause: WHERE. The WHERE clause defines the conditions which individual rows must satisfy. So for example:

update stocks
   set stock_available = 5
     , stock_locked = stock_available + 1  
 where id = 3;

You can effectively create a "exclude" by specifying a NOT condition within the WHERE. So (poor use a not condition, but it will demonstrate)

update stocks
   set stock_available = 7
     , stock_locked = stock_available + 1  
 where id NOT < 4;

The WHERE performs the exactly the same for select and delete statements. Namely to define the rows that qualify for the specified operation.

So it is as simple as `update stocks set stock_available = 5, stock_locked = stock_available + 1 where id = id;` — coterobarros, Aug 29 '20 at 17:39

How to reuse a newly assign column in a PostgreSQL update sentence

1 Answers1