Sequentially update a column with a decrementing value
I need a script that will update the "QTY_REQ" column in the PLSQL table in the screen cap below sequentially row by row by dividing out a number (e.g. 7) among the rows. I want to start with the row with the highest value in QTY (QTY 4, LOC 10800B41) and enter 4 for QTY_REQ. Then go to the second row (QTY 2, LOC 10800A01) and enter 2 for QTY_REQ. Then finally go to the third row (QTY 2, LOC 10800B01) and enter 1 for QTY_REQ. So I evenly divide out the values and stop when the total is reached. The number could be 5 and would just fill in the first two rows (4 entered for for QTY_REQ for the first and 1 entered for for QTY_REQ for the second) Important point; the value that is entered for QTY_REQ cannot be higher than the value that is in the QTY column for that row. If someone can help with this that would be appreciated, thank you.
The script that creates the table can be downloaded here https://file.io/fryZeKgxFMZG
Someone on another forum gave me this solution (which seems to work)
create or replace PROCEDURE set_qty_req (v_distribute_in IN FLOAT,pick_key IN VARCHAR2,p_no IN VARCHAR2) as
BEGIN declare
v_distribute FLOAT := v_distribute_in; BEGIN FOR i IN (SELECT ROWID AS rid, qty FROM MATLIST2 WHERE qty_req IS NULL AND PICK_KEY =pick_key AND PART_=p_no ORDER BY qty DESC, ROWID) LOOP IF v_distribute >= i.qty THEN UPDATE MATLIST2 SET qty_req = i.qty WHERE ROWID = i.rid AND PICK_KEY =pick_key AND PART_=p_no; commit; v_distribute := v_distribute - i.qty; commit; ELSIF v_distribute < i.qty THEN UPDATE MATLIST2 SET qty_req = v_distribute WHERE ROWID = i.rid AND PICK_KEY =pick_key AND PART_=p_no; v_distribute := v_distribute - v_distribute; commit; END IF; IF v_distribute = 0 THEN EXIT; END IF; END LOOP; END;
END;