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.

where current of c_f;? Why you use c_f? Shouldn't it be your cursor name? — Kostadin Slavkov, Mar 09 '17 at 17:19
I have tested your code. It produces the results shown in your Excel screenshot except that `col3` in the first row of the window is null rather than the current value of `col1`. That's easily fixed with a `nvl()` or something. If you think your problem is wider than that you need to post a *complete* (yet small) reproducible test case. — APC, Mar 09 '17 at 18:11
You can update the table row by row within a proc, but this is a slow option. Some say, row by row = slow by slow. You could do this entirely in SQL. You may be testing/trying to learn pl/sql, which is fine, just understand this is not the best way to do what you are doing. — unleashed, Mar 09 '17 at 19:02
c_f was a misspelling mistake, it should be cur. Edited, but still not working. — Web-GIS entrepreneur, Mar 10 '17 at 08:53

score 1 · Accepted Answer · answered Mar 10 '17 at 12:46

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;

It worked for me by looking at yours, but I had to do some tweaking. See below. — Web-GIS entrepreneur, Mar 10 '17 at 16:20

Web-GIS entrepreneur · Answer 2 · 2017-03-13T08:53:52.450

0

In the end, following other's advices, this SQL sentence worked for me:

MERGE INTO TABLE1 tab 
    USING (SELECT ID,(NVL(COL1,0) - NVL(LAG(NVL(COL1,0),1) OVER (PARTITION BY NAME order by ID),0)) VAL FROM TABLE1) a 
    ON (tab.ID = a.ID)
    WHEN matched THEN
      UPDATE SET tab.COL3 = a.VAL;

edited Mar 13 '17 at 08:53

answered Mar 10 '17 at 16:27

Web-GIS entrepreneur

154
3
7

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

2 Answers2