Help with update query

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 Wed Sep 22 2004 - 15:45:40 CDT