Re: Simple FOR loop

Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl> wrote in message news:<7l2s8vofg390vs06vd9vtfca2vbh897hcd_at_4ax.com>...

> Yes it can be done, but as you don't refer to l_dateval.date_col in
> your update statement it very much looks like the update statement is
> wrong.
> Other than that I'm not sure why you don't simply code a subquery in
> the where clause and forget about the for loop. Most likely you don't
> need it at all, and it will be more performat without pl/sql
>

Sorry about that. Too quick copy/paste.
It should of course read

for l_dateval in (select distinct date_col from slv) loop

    update slv
    set slv.settledayhist = (select s.settleday from statement s where s.id = slv.setl_id)

    where slv.settleday = l_dateval
    and slv.settledayhist IS NULL;
end loop;

I'm posting thru Google Groups, and it takes very long to see my own post, so a follow-up to correct myself is difficult. 'Nuff said.

The reason why I want to split it, is to keep the number of rows touched by each statement down a little bit. This is typically part of a weekly job, with a couple of million rows per day, and a weekly total rows for update in the 15-mill range.

I'm trying to keep the update shorter, with less rollback usage, not necessarily faster, and splitting it into 7 chunks of one day seemed like a good idea at the time.

Sorry for the confusion. I'll go wear a silly hat and stand in the corner for a while.

Knut Meidal Received on Mon Apr 07 2003 - 11:10:09 CDT