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  

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 '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 '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 '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 '19 at 9:37

You can use either a window function:

with ct as
      (sum(amount) over (order by id rows between 2 preceding and 1 preceding)) / 2 as new_amount
update t
set    amount = ct.new_amount
    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)
    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.