In PL/SQL, how do you update a row based on the next row?

Question

I'm using Oracle PL/SQL.

I have a timestamped table T, and I want to set a row's value for column A to be the same as that of the previous row, if they're sorted by columns B and Timestamp, provided that the timestamps are not different by more than 45 seconds.

In pseudocode, it's something like:

UPDATE T t_curr
  SET A =
    (SELECT A
      FROM T t_prev
      INNER JOIN t_curr
        ON (t_prev is the row right before t_curr, when you sort by B and Timestamp)
          AND t_curr.Timestamp - t_prev.Timestamp < 45
    )

I tried this:

UPDATE T t_curr
  SET A =
    (SELECT A
      FROM T t_prev
      INNER JOIN t_curr
        ON RANK (t_curr)
          OVER (B, Timestamp)
          = 1 + RANK (t_prev)
          OVER (B, Timestmap)
          AND t_curr.Timestamp - t_prev.Timestamp < 45
    )

But I got:

Error(38,16): PL/SQL: ORA-00934: group function is not allowed here

pointing at the first instance of RANK.

What did I do wrong, and how do I get this right?

Answer 1

Try using a merge statement. Not sure it quite does what you want but it should work. Unfortunately the insert clause is necessary) but shouldn't ever be called.

merge into t a
using (
  select 
    A, 
    B, 
    timestamp, 
    lag(A) over (order by id, timestamp) as prior_A,
    lag(timestamp) over (order by B, timestamp) as prior_timestamp
  from t) b
on  (a.B = b.B)
when matched then 
  update set a.a = case when b.timestamp-b.prior_timestamp <= 45 
    then b.prior_A else b.A end
when not matched then insert (B) values (null)

Answer 2

Can you try something like this:

update x 
set x = y.A
from T x
join T y
where x.B = (select MAX(B) from T where B < y.B)
and x.Timestamp = (select MAX(Timestamp) from T where Timestamp < y.Timestamp)
and y.Timestamp - x.Timestamp < 45