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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
