Re: Help with update query

Hmmm, your first update approach (SQL 1) should have worked nicely. Tried simulating it using my own test table (in 9i) and this type of update with analytics shouldn't pose any problem.

Your table stats accurate? Mind if we look at the execution plan?

Cheers!

powels10_at_nationwide.com (Scott Powell) wrote in message news:<94a6b39a.0409221245.758d6711_at_posting.google.com>...
> Hi, not sure if this is the correct group to post this question to, if
> not please let me know where it should be posted...
>
> I'm trying to write an update statement to populate a "counter" column
> in our calendar table. "Business Day Sequence" is just a numerical
> counter that starts with 1 for the very first business day and counts
> up from there. The following SELECT statement perfectly shows the data
> that I need:
>
> select calendar_date, rank() over (order by calendar_date)
> from calendar
> where business_day = 'Y'
>
> I'm having a horrible time trying to convert this very simple SELECT
> into the proper UPDATE statement. Either its coming up running so
> slowly that we could never use it in production, or I'm having to
> create temp tables, triggers, etc. to get this to work (which I don't
> want to do...database changes take weeks to move into production...)
>
> How can I convert this simple select into an update that works well?
> I'm sure I did it in the past...but now I can't remember how.
>
> Thanks,
> Scott
>
> p.s. some of the SQL I've tried is:
>
> SQL 1: Takes forever (but doesn't need temp tables...), I think its
> running the correlated subquery for each row instead of just once?
>
> update calendar main
> set business_day_sequence =
> (select rank(main.calendar_date) within group (order by
> sub.calendar_date)
> from calendar sub
> where sub.business_day = 'Y')
> where business_day = 'Y'
>
> SQL 2: runs fast but needs temp table:
>
> truncate table calendar_help;
>
> insert into calendar_help
> select calendar_date, rank() over (order by calendar_date) bus_seq
> from calendar
> where business_day = 'Y';
>
> update calendar a
> set business_day_sequence =
> (select bus_seq
> from calendar_help b
> where a.calendar_date = b.calendar_date)
> where business_day = 'Y'
Received on Thu Sep 23 2004 - 02:19:37 CDT