#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    Loop through records in a table and update


    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. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    You don't need to update the data, you can simply retrieve it with a single statement:
    Code:
    select id, 
          amount, 
          the_date,
          lag(amount) over (partition by id order by the_date) as prev_amount
    from the_table
    order by id, the_date;
    Now if you insist on updating it, this would should it:
    Code:
    merge into the_table t1
    using (
       select id, 
              amount, 
              the_date,
              lag(amount) over (partition by id order by the_date) as prev_amount
       from the_table
    ) t2 on (t1.id = t2.id and t1.amount is null)
    when matched then 
      update
         set t1.amount = t2.prev_amount;
    (Not tested)
    Last edited by shammat; February 2nd, 2012 at 03:28 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2012
    Posts
    3
    Rep Power
    0

    Thanks for the reply


    Well, I just want to see if the second example does it but is throwing an error "SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "T1"."NEWFLOAT"
    38104. 00000 - "Columns referenced in the ON Clause cannot be updated: %s"
    *Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause"

    Would you know why?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,787
    Rep Power
    349
    Originally Posted by Midway
    Well, I just want to see if the second example does it but is throwing an error "SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "T1"."NEWFLOAT"
    38104. 00000 - "Columns referenced in the ON Clause cannot be updated: %s"
    *Cause: LHS of UPDATE SET contains the columns referenced in the ON Clause"

    Would you know why?
    Well the error message is quite obvious.
    Try this one:
    Code:
    merge into the_table t1
    using (
       select id, 
              amount, 
              the_date,
              lag(amount) over (partition by id order by the_date) as prev_amount
       from the_table
    ) t2 on (t1.id = t2.id)
    when matched then 
      update
         set case 
                when t1.amount is null then = t2.prev_amount
                else t1.amount = t1.amount
             end

IMN logo majestic logo threadwatch logo seochat tools logo