possible to do a top -N update?

From: <rgaffuri_at_cox.net>
Date: Fri, 24 Oct 2003 07:09:38 -0800
Message-ID: <F001.005D4371.20031024070938@fatcity.com>

Im familiar with top n queries. Im trying to tune an update and try to do it in one update statement instead of a query. Im basically trying to update only the top N fields.

Here is the cursor(I have rewritten it as an analytic function, but I really want it as a single update. any suggestions?

I re-wrote the cursor as follows, which is much faster, but I want to get away from pl/sql

 select *

    from (select pk, date,

             dense_rank()
             over (partition by pk
                   order by date desc)
             tab from mytable a) tab
     where tab = 1

here is the cursor:

declare
  cursor c_update is
    select pk, last_day(date) monthend_date,

           max(date) max_date
    from mytable
    group by pk, last_day(date);

  row integer;
  l_date date;

begin

  select last_day (add_months(sysdate, -1) ) into l_date   from dual;

  row := 0;
  for update_rec in c_update
  loop
    if update_rec.max_date <= l_date then

      update mytable
      set monthend_date = update_rec.monthend_date
      where pk = update_rec.pk
        and date = update_rec.max_perfdate;

  

    end if;

  end loop;

  commit;
end;
/

I tried re-writing it as follows: but i get errors on the order by. any other possibilities?

    update mytable a
    set monthend_date = (select last_day(date)

                         from  mytable b
                         where a.pk = b.pk 
                         and rownum = 1)
    where (pk,date) in (select pk,date
                          from mytable c
                         where c.pk = a.pk
                           and rownum = 1
                        order by date desc)

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Oct 24 2003 - 10:09:38 CDT