1

I need help with my sql script.

Inventory is a table which contains orderunit, location, siteid and itemnum. This update aims to change all "orderunit = NULL" by the same item (itemnum) in such a way :

%_TAB => %_TD

and

%_TD => %_ACM

I want the same result with cursors :

UPDATE INVENTORY A 
SET A.orderunit = (SELECT B.orderunit 
                   FROM INVENTORY B 
                   WHERE B.location = A.siteid || '_TD' 
                     AND B.itemnum = A.itemnum) 
WHERE A.location = A.siteid || '_TAB' 
  AND A.orderunit IS NULL;

UPDATE INVENTORY A 
SET A.orderunit = (SELECT B.orderunit 
                   FROM INVENTORY B 
                   WHERE B.location = A.siteid || '_ACM' 
                     AND B.itemnum = A.itemnum) 
WHERE A.location = A.siteid || '_TD' 
  AND A.orderunit IS NULL;

I tried to make this and it seems not to work.

DECLARE
    inv_itemnum INVENTORY.itemnum%TYPE
    inv_location INVENTORY.location%TYPE
    inv_siteid INVENTORY.siteid%TYPE
    inv_orderunit INVENTORY.orderunit%TYPE

    CURSOR c_inventory_tab IS
        SELECT orderunit FROM INVENTORY WHERE location = siteid || '_TAB' AND orderunit IS NULL
        FOR UPDATE;
    CURSOR c_inventory_td IS
        SELECT orderunit FROM INVENTORY WHERE location = siteid || '_TD' AND orderunit IS NULL
        FOR UPDATE;
    CURSOR c_inventory_tabB
        SELECT orderunit FROM INVENTORY WHERE location = siteid || '_TD' AND itemnum = itemnum;
    CURSOR c_inventory_tdB
        SELECT orderunit FROM INVENTORY WHERE location = siteid || '_ACM' AND itemnum = itemnum;
BEGIN
    FOR i IN c_inventory_tab
    LOOP
    UPDATE INVENTORY
    SET orderunit = c_inventory_tabB;
    END LOOP;
END;

Thanks for your help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
robinwood13
  • 179
  • 1
  • 1
  • 8
  • 1
    Is there a reason to switch to cursors? RDBMS Updates using set based processing are typically far more efficient. Is this just a learning exercise or do you expect a performance gain? which can happen [link](http://sqlmag.com/t-sql/set-based-vs-cursor-based-solutions-running-aggregates) – xQbert Nov 24 '15 at 21:28
  • It is a learning exercise – robinwood13 Nov 25 '15 at 06:59
  • You need to say `UPDATE ... WHERE CURRENT OF c_inventory_tab`. You also need to move the `c_inventory_tabB` cursor yourself using `FETCH` commands, until both cursors are pointing at the same `itemnum`. You can do this _much_ more efficiently if your query results are sorted. – Nick Barnes Nov 25 '15 at 14:29

0 Answers0