Question 315242 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Update PostegreSQL with previous row value

I try to update my base SellAll with the value of a previous stock. I have this request so far :

UPDATE SellAll 
set S_stock = (S_in - S_out) + monthn1.stock
FROM  
(
    SELECT  
        LAG(S_stock,1,0) OVER (ORDER BY S_famille,S_year, S_month) AS stock, 
        S_id
    FROM SellAll
) AS monthn1
WHERE SellAll.S_id = monthn1.S_id;

But i don t have the result wanted.
For example i have this grid

id year month famille stock in out
573 2023 9 2 -6 20 18
574 2023 10 2 1 18 21
575 2023 11 2 -7 21 20
576 2023 12 2 2 25 20

With my request i have this

id year month famille stock in out
573 2023 9 2 -6 20 18
574 2023 10 2 0 18 21
575 2023 11 2 2 21 20
576 2023 12 2 -2 25 20

But i want this :

id year month famille stock in out
573 2023 9 2 -6 20 18
574 2023 10 2 -3 18 21
575 2023 11 2 -2 21 20
576 2023 12 2 3 25 20

I want to subtract (in – out) + stock of previous row
Can you help me ?

I want to subtract (in – out) + stock of previous row in PostgreSQL

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks for your answer !

    I m more close of the good result with this request :

    UPDATE SellAll set S_stock = monthn1.stock FROM
    ( SELECT (S_in - S_out)+LAG(S_stock,1,0) OVER (ORDER BY S_famille,S_year, S_month) AS stock, S_id FROM SellAll ) AS monthn1 WHERE SellAll.S_id = monthn1.S_id and monthn1.stock<>0;

    But still not but may be there is something that i dont understand because the result seems good at the moment of the update but i want the good stock of the previous row after the update and it seems not the case.

    For example I have this grid :

    enter image description here

    With my request i have this result

    enter image description here

    But i want enter image description here


  2. 0

    first CHECK is the value of stock is correct

    SELECT  
  *,
  LAG(S_stock,1,0) OVER (ORDER BY S_famille,S_year, S_month) AS stock
FROM SellAll

    it seems not correct, but

    this should look better:

    SELECT  
  *,
  LAG((S_in - S_out) +S_stock,1,0) OVER (ORDER BY S_famille,S_year, S_month) AS stock
FROM SellAll

    TEST it:

    UPDATE SellAll set S_stock =  monthn1.stock
FROM  
(
    SELECT  LAG((S_in - S_out) +S_stock,1,0) OVER (ORDER BY S_famille,S_year, S_month) AS stock, 
            S_id
    FROM SellAll
) AS monthn1
WHERE SellAll.S_id = monthn1.S_id and monthn1.stock<>0;

    output:

    s_id s_year s_month s_famille s_stock s_in s_out
    573 2023 9 2 -6 20 18
    574 2023 10 2 -4 18 21
    575 2023 11 2 -2 21 20
    576 2023 12 2 -6 25 20

    P.S. These are not the value you want, but the seem correct to /me…. 😉

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search