Updating a column in an Oracle procedure based on other two columns and using a LAG function

Question

I'm trying to update a column by using the following procedure, it uses the LAG function as the result is calculated by subtracting the previous value to the current value as it can be seen in the image below, but it does not work so far as it populates the col3 with values that I don't expect at all..

Basically what I want is (use Excel to illustrate the problem):

And I want this for every different name in the table.

create or replace procedure proc1 as
  cursor cur is
    SELECT (col1 - LAG(col1,1) OVER (PARTITION BY name order by ID)) 
    FROM table1 
    for update;
    var_diff NUMBER;
begin
  open cur;
  loop
    fetch cur into var_diff;
    exit when cur%NOTFOUND;
    update table1 set col3=var_diff where current of cur;
  end loop;
  close cur;
end proc1;
/

exec proc1;
commit;

I know for example that the SQL statement used above works:

SELECT col1, 
       LAG(col1,1) OVER (PARTITION BY name order by ID), 
       (col1 - LAG(col1,1) OVER (PARTITION BY name order by ID)) 
FROM table1;

But I cannot make the procedure work.

Answer 1

What i feel is this can be easily done via SQL only. We should/must always try to resolve the problem by SQL method if possible then look for PLSQL options if required. This requirement can be easily done by MERGE or simple UPDATE statement. I have illustrated both PLSQL ND sql way. Hope this helps.

--SQL Way better then PLSQL way
merge INTO TABLE1 tab USING
(SELECT (COL1 - LAG(COL1,1) over (partition BY name order by id)) COL1
FROM TABLE1
)a ON (TAB.COL1 = a.COL1)
WHEN matched THEN
  UPDATE SET COL3 = a.COL1 WHERE col1 = a.col1;

--PLSQL way but will be slow as we are doing it row-row aka slow-by-slow provcessing
CREATE OR REPLACE
PROCEDURE proc1
AS
BEGIN
  for I        in
  (SELECT (COL1 - LAG(COL1,1) over (partition BY name order by id)) col3,col1
  FROM table1
  )
  LOOP
    UPDATE table1 SET col3=i.col3 where col1 = i.col1;
  END LOOP;
END proc1;