1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Loop through records in a table and update

Discussion in 'SQL PL/SQL' started by Midway, Feb 2, 2012.

  1. Midway

    Midway Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi there,

    Need some help here please. Sorry new to Oracle. I'm working with Oracle 10g.

    I have a table like this;

    ID Amount Date
    123 5000 Oct-07-2011
    123 null Oct-09-2011
    124 7000 Oct-14-2011
    124 null Oct-17-2011
    124 null Oct-24-2011

    What I'm trying to do here is loop thruogh the records and update the amount that's null with the previous amount with the same ID.

    Some sample code that I follow for this?

    Thanks very much for your help.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No need to loop through the records. You can do this in straight SQL by using LAG.

    Try with LAG and let us know the feedback.
     
  3. Midway

    Midway Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks for replying,

    I am trying the LAG function but no luck.

    create table t(id number, amount number, thedate date);

    insert into t
    values
    (123, 8000, sysdate-9); --January

    insert into t
    values
    (123, 9000, sysdate-8); --February

    insert into t
    values
    (123, null, sysdate-7); --Arpil


    select * from t;


    select id, thedate, amount, nvl(amount, prev_amount) as adjusted_amount
    from
    (select id, amount, thedate, lag(amount ignore nulls) over (partition by id order by thedate) prev_amount
    from t)
    order by id, thedate;


    I'm working with 10g. when run the last select statement, I'm getting an Oracle error; "ORA-00907: missing right parenthesis"

    but I remove the Ignore Nulls statement in the LAG function, the stament runs but with no the desired results. Would you know if the LAG function with ignore nulls works on 10g?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    yes it works in 10g. But ignore null works with 11g I guess.

    Anyway, currently I dont have access to DB to show you the result.
     
  5. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    A long way.

    Code (SQL):
    SQL> CREATE TABLE Midway_tab (acct_id NUMBER, amt NUMBER , date1 DATE);

    TABLE created.

    SQL> INSERT INTO midway_tab VALUES(123, 5000, '07-oct-2011');

    1 ROW created.

    SQL> INSERT INTO midway_tab VALUES(123, NULL ,'09-oct-2011');

    1 ROW created.

    SQL> INSERT INTO midway_tab VALUES (124 ,7000, '14-oct-2011');

    1 ROW created.

    SQL> INSERT INTO midway_tab VALUES(124, NULL, '17-oct-2011');

    1 ROW created.

    SQL> INSERT INTO midway_tab VALUES(124, NULL, '24-oct-2011');

    1 ROW created.

    SQL> SELECT *FROM midway_tab;

       ACCT_ID        AMT DATE1
    ---------- ---------- ---------
           123       5000 07-OCT-11
           123            09-OCT-11
           124       7000 14-OCT-11
           124            17-OCT-11
           124            24-OCT-11

    SQL> SELECT acct_id, date1, amt,
      2         CASE
      3            WHEN amt IS NULL
      4               THEN (SELECT amt
      5                       FROM midway_tab
      6                      WHERE ROWID = (SELECT MIN (ROWID)
      7                                       FROM midway_tab
      8                                      WHERE acct_id = t.acct_id AND amt IS NOT NULL))
      9            ELSE amt
     10         END amt1
     11    FROM midway_tab t;

     
       ACCT_ID DATE1            AMT       AMT1
    ---------- --------- ---------- ----------
           123 07-OCT-11       5000       5000
           123 09-OCT-11                  5000
           124 14-OCT-11       7000       7000
           124 17-OCT-11                  7000
           124 24-OCT-11                  7000

    SQL>
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The Rowid Concept may not work well always.. . Especially when there are some updates and inserts in the table.
     
    kiran.marla likes this.
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The LAG with IGNORE KeyWord will not work in version 10.2.

    The alternative is to use Subquery that makes the same logic of Ignore nulls.

    Code (SQL):

    SQL> SELECT * FROM v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS FOR Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    SQL> -- The data set
    SQL> WITH REC AS (
      2  SELECT 1 id, 100 amount, TO_DATE('01/01/2011','DD/MM/YYYY') thedate FROM DUAL
      3  UNION
      4  SELECT 1 , NULL, TO_DATE('01/02/2011','DD/MM/YYYY') DT FROM DUAL
      5  UNION
      6  SELECT 2 , 200, TO_DATE('01/03/2011','DD/MM/YYYY') DT FROM DUAL
      7  UNION
      8  SELECT 2 , NULL, TO_DATE('01/04/2011','DD/MM/YYYY') DT FROM DUAL
      9  UNION
     10  SELECT 2 , NULL, TO_DATE('01/05/2011','DD/MM/YYYY') DT FROM DUAL
     11  UNION
     12  SELECT 2 , NULL, TO_DATE('01/06/2011','DD/MM/YYYY') DT FROM DUAL
     13  UNION
     14  SELECT 3 , 150, TO_DATE('01/07/2011','DD/MM/YYYY') DT FROM DUAL
     15  UNION
     16  SELECT 3 , NULL, TO_DATE('01/08/2011','DD/MM/YYYY') DT FROM DUAL
     17  UNION
     18  SELECT 3 , 250, TO_DATE('01/09/2011','DD/MM/YYYY') DT FROM DUAL
     19  UNION
     20  SELECT 3 , NULL, TO_DATE('01/10/2011','DD/MM/YYYY') DT FROM DUAL  )
     21  SELECT *
     22  FROM rec;

            ID     AMOUNT THEDATE
    ---------- ---------- ---------
             1        100 01-JAN-11
             1            01-FEB-11
             2        200 01-MAR-11
             2            01-APR-11
             2            01-MAY-11
             2            01-JUN-11
             3        150 01-JUL-11
             3        250 01-SEP-11
             3            01-AUG-11
             3            01-OCT-11

    10 ROWS selected.

    SQL>
    SQL>
    SQL> -- Data set with New Amount --> With Subquery
    WITH REC AS (
    SQL>   2  SELECT 1 id, 100 amount, TO_DATE('01/01/2011','DD/MM/YYYY') thedate FROM DUAL
    UNION
      3    4  SELECT 1 , NULL, TO_DATE('01/02/2011','DD/MM/YYYY') DT FROM DUAL
      5  UNION
      6   SELECT 2 , 200, TO_DATE('01/03/2011','DD/MM/YYYY') DT FROM DUAL
      7  UNION
      8  SELECT 2 , NULL, TO_DATE('01/04/2011','DD/MM/YYYY') DT FROM DUAL
      9  UNION
     10  SELECT 2 , NULL, TO_DATE('01/05/2011','DD/MM/YYYY') DT FROM DUAL
     11  UNION
     12  SELECT 2 , 125, TO_DATE('01/06/2011','DD/MM/YYYY') DT FROM DUAL
     13  UNION
     14  SELECT 2 , 50, TO_DATE('01/07/2011','DD/MM/YYYY') DT FROM DUAL
     15  UNION
     16  SELECT 2 , NULL, TO_DATE('01/08/2011','DD/MM/YYYY') DT FROM DUAL
     17  UNION
     18  SELECT 3 , 250, TO_DATE('01/09/2011','DD/MM/YYYY') DT FROM DUAL
     19  UNION
     20  SELECT 3 , NULL, TO_DATE('01/10/2011','DD/MM/YYYY') DT FROM DUAL  )
     21  SELECT id, AMOUNT,THEDATE,
     22             (SELECT DISTINCT MAX(R.AMOUNT) keep ( dense_rank  LAST ORDER BY R.THEDATE ASC ) OVER ( partition BY id)
     23              FROM REC R
     24              WHERE R.id = R1.ID
     25              AND R.AMOUNT IS NOT NULL
     26              AND r.THEDATE <= R1.THEDATE)  NEW_AMOUNT
     27  FROM REC r1
     28  ORDER BY THEDATE;

            ID     AMOUNT THEDATE   NEW_AMOUNT
    ---------- ---------- --------- ----------
             1        100 01-JAN-11        100
             1            01-FEB-11        100
             2        200 01-MAR-11        200
             2            01-APR-11        200
             2            01-MAY-11        200
             2        125 01-JUN-11        125
             2         50 01-JUL-11         50
             2            01-AUG-11         50
             3        250 01-SEP-11        250
             3            01-OCT-11        250

    10 ROWS selected.

    SQL>
    SQL>

     
     
    kiran.marla likes this.
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    And prior to Oracle 9i, the following code without analytical function will work. It is an extension of kiran's query.

    Code (SQL):

    SQL> SELECT * FROM v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle DATABASE 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS FOR Linux: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    SQL> -- Data set with New Amount --> With Subquery Prior to 9i
    WITH REC AS (
    SQL>   2  SELECT 1 id, 100 amount, TO_DATE('01/01/2011','DD/MM/YYYY') thedate FROM DUAL
      3  UNION
      4  SELECT 1 , NULL, TO_DATE('01/02/2011','DD/MM/YYYY') DT FROM DUAL
      5  UNION
      6  SELECT 2 , 200, TO_DATE('01/03/2011','DD/MM/YYYY') DT FROM DUAL
      7  UNION
      8  SELECT 2 , NULL, TO_DATE('01/04/2011','DD/MM/YYYY') DT FROM DUAL
      9  UNION
     10  SELECT 2 , NULL, TO_DATE('01/05/2011','DD/MM/YYYY') DT FROM DUAL
     11  UNION
     12  SELECT 2 , 125, TO_DATE('01/06/2011','DD/MM/YYYY') DT FROM DUAL
     13  UNION
     14  SELECT 2 , 50, TO_DATE('01/07/2011','DD/MM/YYYY') DT FROM DUAL
     15  UNION
     16  SELECT 2 , NULL, TO_DATE('01/08/2011','DD/MM/YYYY') DT FROM DUAL
     17  UNION
     18  SELECT 3 , 250, TO_DATE('01/09/2011','DD/MM/YYYY') DT FROM DUAL
     19  UNION
     20  SELECT 3 , NULL, TO_DATE('01/10/2011','DD/MM/YYYY') DT FROM DUAL  )
     21  SELECT id, AMOUNT,THEDATE,
     22             (SELECT AMOUNT
     23              FROM REC R2
     24              WHERE thedate =(SELECT MAX(thedate)
     25                                FROM REC R
     26                               WHERE R.id = R1.id
     27                                 AND R.AMOUNT IS NOT NULL
     28                                AND R.THEDATE <= R1.THEDATE)
     29              ) NEW_AMOUNT
     30  FROM REC r1
     31  ORDER BY THEDATE;

            ID     AMOUNT THEDATE   NEW_AMOUNT
    ---------- ---------- --------- ----------
             1        100 01-JAN-11        100
             1            01-FEB-11        100
             2        200 01-MAR-11        200
             2            01-APR-11        200
             2            01-MAY-11        200
             2        125 01-JUN-11        125
             2         50 01-JUL-11         50
             2            01-AUG-11         50
             3        250 01-SEP-11        250
             3            01-OCT-11        250

    10 ROWS selected.

    SQL>
     
     
  9. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    The Lag Produces the following Output

    Code (SQL):


     SELECT acct_id,amt,lag(amt IGNORE NULLS) OVER(ORDER BY acct_id) AS amount
      FROM midway_tab;

    CT_ID       AMT    AMOUNT
    ----- --------- ---------
      123
      123      5000
      124                5000
      124                5000
      124      7000    5000

     

    Here,the amount for id 124 is changed to 5000 although the original is 7000.


    Code (SQL):




    SQL> UPDATE midway_tab t SET t.amt=
      2  (SELECT l.amt FROM midway_tab l WHERE l.amt IS NOT NULL AND t.acct_id=l.acct_id) WHERE
      3  t.amt IS NULL;

     

    The output for above query is :

    Code (SQL):



    SQL> SELECT * FROM midway_tab;

      ACCT_ID       AMT DATE1
    --------- --------- ---------
          123      5000 07-OCT-11
          123      5000 09-OCT-11
          124      7000 14-OCT-11
          124      7000 17-OCT-11
          124      7000 24-OCT-11
     
     
    Sadik likes this.