0

I want to update an row according to the values of two rows before the row how can i do that?

Let's assume my table looks like this

ID         amount

1           1
2           3
7           5

And i want to do this

UPDATE amount 
FROM table 
SET amount = (AmounToFRowOneBefore + AmounToFRowTwoBefore)/2  
WHERE ID=7;

Expected output would be

 ID         amount

    1           1
    2           3
    7           2

And if I do this I don't know anything about the two rows before so no ID or anything is there a query to select the values before the selected row?

  • Adding the expected output would be helpful. Also tag your RDBMS, like SQL Server or MySQL or Oracle, .... – Arulkumar Jul 19 at 8:31
  • Depending on your dbms and version you can use the LAG() function to refer to these values. docs.microsoft.com/en-us/sql/t-sql/functions/… – J. Maes Jul 19 at 8:32
  • And how can i use lag to get the value of the n row before the selected one ? – DataLordDev Jul 19 at 8:42
  • I don't know anything about the two rows before I suppose there is an ORDER in your data. – McNets Jul 19 at 9:37
1

You can use either a window function:

with ct as
(
  select
      id,
      amount,
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
  from
      t
)
update t
set    amount = ct.new_amount
from
    ct
where
    t.id = ct.id
    and ct.id = 3;

Or use a subquery that returns the 2 previous rows:

update t
set amount = (select sum(t3.amount) / 2
             from (select amount 
                   from t t2
                   where t2.id < t.id 
                   order by id desc 
                   limit 2) t3)
where
    t.id = 3;

Check them for performance.

select * from t;
id |             amount
-: | -----------------:
 1 |                  1
 2 |                  3
 3 | 2.0000000000000000

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.