Loop through records in a table and update

Dev Shed Forums Sponsor:

February 2nd, 2012, 10:31 AM

Midway

Registered User

Join Date: Feb 2012

Posts: 3

Time spent in forums: 1 h 13 m 33 sec
Reputation 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.

February 2nd, 2012, 10:58 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,504

Time spent in forums: 1 Month 2 Weeks 5 Days 2 h 15 m 31 sec
Reputation Power: 283

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 02:28 PM.

February 2nd, 2012, 11:27 AM

Midway

Registered User

Join Date: Feb 2012

Posts: 3

Time spent in forums: 1 h 13 m 33 sec
Reputation 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?

February 2nd, 2012, 02:28 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,504

Time spent in forums: 1 Month 2 Weeks 5 Days 2 h 15 m 31 sec
Reputation Power: 283

Quote:

Originally Posted by Midway

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