OraFAQ Forum: SQL & PL/SQL » Sequentially update a column with a decrementing value

Home » SQL & PL/SQL » SQL & PL/SQL » Sequentially update a column with a decrementing value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Sequentially update a column with a decrementing value [message #689333]

Fri, 24 November 2023 13:28

madfeen
Messages: 5
Registered: November 2023

Junior Member

I want to update the "QTY_REQ" column in the PLSQL table in the screen cap attached 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.

Attachment: table screen cap.png
(Size: 27.64KB, Downloaded 450 times)

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689334 is a reply to message #689333]

Fri, 24 November 2023 13:51

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
The test case must be representative of your data and different cases you have to handle.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689335 is a reply to message #689334]

Fri, 24 November 2023 14:33

madfeen
Messages: 5
Registered: November 2023

Junior Member

It is a script I need to update the "QTY_REQ" columns. The script to create the table can be downloaded from here:https://file.io/fryZeKgxFMZG
I am using Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0
I don't have a test case but I know I need a for loop to do what is required.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689336 is a reply to message #689335]

Fri, 24 November 2023 14:44

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I will surely not access a site I don't know and download some file.
You can post its content here (don't forget to use code tags).
You can write the INSERT statements for the data you showed in your image, this is a test case (please read the links).

Using the standard EMP table we can play with the salary column asking for a total we want:

SQL> select empno, sal from emp;
     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100
      7900        950
      7902       3000
      7934       1300

14 rows selected.

We request for a total of 10000 or 20000 (for instance):

SQL> def req=10000
SQL> with
  2    data as (
  3      select empno, sal,
  4             row_number() over (order by sal desc, empno) rn
  5      from emp
  6    ),
  7    compute (empno, sal, rn, sal_req, total) as (
  8      select empno, sal, rn,
  9             case when sal >= &req then &req else sal end sal_req,
 10             case when sal >= &req then &req else sal end total
 11      from data
 12      where rn = 1
 13      union all
 14      select d.empno, d.sal, d.rn,
 15             case when c.total+d.sal >= &req then &req-c.total else d.sal end,
 16             c.total + case when c.total+d.sal >= &req then &req-c.total else d.sal end
 17      from compute c, data d
 18      where d.rn = c.rn + 1
 19        and c.total < &req
 20    )
 21  select empno, sal, sal_req
 22  from compute
 23  order by sal desc, empno
 24  /
     EMPNO        SAL    SAL_REQ
---------- ---------- ----------
      7839       5000       5000
      7788       3000       3000
      7902       3000       2000

3 rows selected.

SQL> def req=20000
SQL> /
     EMPNO        SAL    SAL_REQ
---------- ---------- ----------
      7839       5000       5000
      7788       3000       3000
      7902       3000       3000
      7566       2975       2975
      7698       2850       2850
      7782       2450       2450
      7499       1600        725

7 rows selected.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689337 is a reply to message #689336]

Fri, 24 November 2023 14:51

madfeen
Messages: 5
Registered: November 2023

Junior Member

The code that creates the table is below. I have also attached it as a file

  CREATE TABLE "IFSAPP"."MATLIST2" 
   (	"LOC" VARCHAR2(1000 BYTE), 
	"QTY" VARCHAR2(1000 BYTE), 
	"LIN" VARCHAR2(1000 BYTE), 
	"PART_" VARCHAR2(1000 BYTE), 
	"SHOP_ORD" VARCHAR2(1000 BYTE), 
	"PICK_KEY" VARCHAR2(1000 BYTE), 
	"QTY_REQ" VARCHAR2(200 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "IFSAPP_DATA" ;
REM INSERTING into IFSAPP.MATLIST2
SET DEFINE OFF;
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B41','4','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A01','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B01','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B21','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B61','2','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A01','1','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800A21','1','14','1020123','49507','24377',null);
Insert into IFSAPP.MATLIST2 (LOC,QTY,LIN,PART_,SHOP_ORD,PICK_KEY,QTY_REQ) values ('10800B21','1','14','1020123','49507','24377',null);

Attachment: script.sql
(Size: 1.69KB, Downloaded 817 times)

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689338 is a reply to message #689337]

Fri, 24 November 2023 15:06

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try to adapt the query I gave as an example.
Ask if something blocks.

The main point is to order the rows as you want to handle them (see ROW_NUMBER function).

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689339 is a reply to message #689338]

Fri, 24 November 2023 15:57

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_12.1.0.2.0> -- optional SQL*Plus format commands
SCOTT@orcl_12.1.0.2.0> -- to shorten column widths for
SCOTT@orcl_12.1.0.2.0> -- easier readability on this forum:
SCOTT@orcl_12.1.0.2.0> COLUMN LOC      FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN QTY      FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN LIN      FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN PART_    FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN SHOP_ORD FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN PICK_KEY FORMAT A10
SCOTT@orcl_12.1.0.2.0> COLUMN QTY_REQ  FORMAT A10

SCOTT@orcl_12.1.0.2.0> -- starting data before updates:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM MATLIST2 ORDER BY qty DESC, ROWID
  2  /

LOC        QTY        LIN        PART_      SHOP_ORD   PICK_KEY   QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41   4          14         1020123    49507      24377
10800A01   2          14         1020123    49507      24377
10800B01   2          14         1020123    49507      24377
10800B21   2          14         1020123    49507      24377
10800B61   2          14         1020123    49507      24377
10800A01   1          14         1020123    49507      24377
10800A21   1          14         1020123    49507      24377
10800B21   1          14         1020123    49507      24377

8 rows selected.

SCOTT@orcl_12.1.0.2.0> -- updates:
SCOTT@orcl_12.1.0.2.0> DECLARE
  2    -- change the number 7 below to whatever you want
  3    v_distribute  NUMBER := 7;
  4  BEGIN
  5    FOR i IN
  6  	 (SELECT ROWID AS rid, qty
  7  	  FROM	 MATLIST2
  8  	  WHERE  qty_req IS NULL
  9  	  ORDER  BY qty DESC, ROWID)
 10    LOOP
 11  	 IF v_distribute >= i.qty THEN
 12  	   UPDATE MATLIST2
 13  	   SET	  qty_req = i.qty
 14  	   WHERE  ROWID = i.rid;
 15  	   v_distribute := v_distribute - i.qty;
 16  	 ELSIF v_distribute < i.qty THEN
 17  	   UPDATE MATLIST2
 18  	   SET	  qty_req = v_distribute
 19  	   WHERE  ROWID = i.rid;
 20  	   v_distribute := v_distribute - v_distribute;
 21  	 END IF;
 22  	 IF v_distribute = 0 THEN EXIT;
 23  	 END IF;
 24    END LOOP;
 25  END;
 26  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> COMMIT
  2  /

Commit complete.

SCOTT@orcl_12.1.0.2.0> -- ending data after updates:
SCOTT@orcl_12.1.0.2.0> SELECT * FROM MATLIST2 ORDER BY qty DESC, ROWID
  2  /

LOC        QTY        LIN        PART_      SHOP_ORD   PICK_KEY   QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41   4          14         1020123    49507      24377      4
10800A01   2          14         1020123    49507      24377      2
10800B01   2          14         1020123    49507      24377      1
10800B21   2          14         1020123    49507      24377
10800B61   2          14         1020123    49507      24377
10800A01   1          14         1020123    49507      24377
10800A21   1          14         1020123    49507      24377
10800B21   1          14         1020123    49507      24377

8 rows selected.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689340 is a reply to message #689339]

Fri, 24 November 2023 16:14

madfeen
Messages: 5
Registered: November 2023

Junior Member

Thank you Barbara, I tested it just there and it seems to do what I require. Very kind of you, I would never have worked that out by myself, although I spent quite a bit of time trying :)

[Updated on: Fri, 24 November 2023 16:15]

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689341 is a reply to message #689340]

Fri, 24 November 2023 16:50

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

In your sample data, most of your columns had the same values.
You may want to add additional conditions in the where clause of the select statement as shown below.

DECLARE
  -- change the number 7 below to whatever you want
  v_distribute  NUMBER := 7;
BEGIN
  FOR i IN
    (SELECT ROWID AS rid, qty
     FROM   MATLIST2 
     WHERE  qty_req IS NULL
     -- add additional conditions below
     AND    lin = 14
     AND    part_ = '1020123'
     AND    shop_ord = 49507
     AND    pick_key = 24377
     ORDER  BY qty DESC, ROWID)
  LOOP
    IF v_distribute >= i.qty THEN
      UPDATE MATLIST2
      SET    qty_req = i.qty
      WHERE  ROWID = i.rid;
      v_distribute := v_distribute - i.qty;      
    ELSIF v_distribute < i.qty THEN
      UPDATE MATLIST2
      SET    qty_req = v_distribute
      WHERE  ROWID = i.rid;
      v_distribute := v_distribute - v_distribute;
    END IF;
    IF v_distribute = 0 THEN EXIT;
    END IF;
  END LOOP;
END;
/

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689342 is a reply to message #689340]

Sat, 25 November 2023 02:26

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Using pure SQL:

SQL> select * from "MATLIST2" order by qty desc, loc;
LOC               QTY LIN        PART_      SHOP_ORD   PICK_KEY      QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41            4 14         1020123    49507      24377
10800A01            2 14         1020123    49507      24377
10800B01            2 14         1020123    49507      24377
10800B21            2 14         1020123    49507      24377
10800B61            2 14         1020123    49507      24377
10800A01            1 14         1020123    49507      24377
10800A21            1 14         1020123    49507      24377
10800B21            1 14         1020123    49507      24377

8 rows selected.

SQL> def req=7
SQL> merge into MATLIST2 m
  2  using (
  3    with
  4      data as (
  5        select m.*, rowid rid,
  6               row_number() over (order by qty desc, loc) rn
  7        from MATLIST2 m
  8      ),
  9      compute (LOC,QTY, LIN, PART_, SHOP_ORD, PICK_KEY, rid, rn, QTY_REQ, total) as (
 10        select LOC, QTY, LIN,PART_, SHOP_ORD, PICK_KEY, rid, rn,
 11               case when QTY >= &req then &req else QTY end QTY_REQ,
 12               case when QTY >= &req then &req else QTY end total
 13        from data
 14        where rn = 1
 15        union all
 16        select d.LOC, d.QTY, d.LIN, d.PART_, d.SHOP_ORD, d.PICK_KEY, d.rid, d.rn,
 17               case when c.total+d.QTY >= &req then &req-c.total else d.QTY end,
 18               c.total + case when c.total+d.QTY >= &req then &req-c.total else d.QTY end
 19        from compute c, data d
 20        where d.rn = c.rn + 1
 21          and c.total < &req
 22      )
 23    select LOC, QTY, LIN, PART_, SHOP_ORD, PICK_KEY, QTY_REQ, rid
 24    from compute
 25    ) c
 26  on ( m.rowid = c.rid )
 27  when matched then update set QTY_REQ = c.QTY_REQ
 28  /

3 rows merged.

SQL> select * from "MATLIST2" order by qty desc, loc;
LOC               QTY LIN        PART_      SHOP_ORD   PICK_KEY      QTY_REQ
---------- ---------- ---------- ---------- ---------- ---------- ----------
10800B41            4 14         1020123    49507      24377               4
10800A01            2 14         1020123    49507      24377               2
10800B01            2 14         1020123    49507      24377               1
10800B21            2 14         1020123    49507      24377
10800B61            2 14         1020123    49507      24377
10800A01            1 14         1020123    49507      24377
10800A21            1 14         1020123    49507      24377
10800B21            1 14         1020123    49507      24377

8 rows selected.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689348 is a reply to message #689342]

Sun, 26 November 2023 13:54

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Match recognize solution:

merge
  into matlist2 t
  using (
         select  rowid rid,
                 req_qty
           from  matlist2
           match_recognize(
                           order by qty desc
                           measures greatest(0,least(&qty_on_hand + qty - sum(qty),qty)) req_qty
                           all rows per match
                           pattern(p+)
                           define p as 1 = 1
                          )
        ) s
  on (t.rowid = s.rid)
  when matched
    then update
            set t.qty_req = s.req_qty
/
Enter value for qty_on_hand: 7
old   9:                            measures greatest(0,least(&qty_on_hand + qty - sum(qty),qty)) req_qty
new   9:                            measures greatest(0,least(7 + qty - sum(qty),qty)) req_qty

8 rows merged.

SQL> select  *
  2    from  matlist2
  3  /

LOC             QTY LI PART_   SHOP_ PICK_    QTY_REQ
-------- ---------- -- ------- ----- ----- ----------
10800B41          4 14 1020123 49507 24377          4
10800A01          2 14 1020123 49507 24377          1
10800B01          2 14 1020123 49507 24377          2
10800B21          2 14 1020123 49507 24377          0
10800B61          2 14 1020123 49507 24377          0
10800A01          1 14 1020123 49507 24377          0
10800A21          1 14 1020123 49507 24377          0
10800B21          1 14 1020123 49507 24377          0

8 rows selected.

SQL>

SY.

Report message to a moderator

Re: Sequentially update a column with a decrementing value [message #689349 is a reply to message #689348]

Mon, 27 November 2023 02:23

madfeen
Messages: 5
Registered: November 2023

Junior Member

Thank you to everyone for their replies. I will have to adjust it slightly because in PROD there will be a lot of different values in the table. But I think I can do that.

Report message to a moderator

Previous Topic:	SQL QUERY
Next Topic:	Transform row data into columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 10:03:44 CDT 2024