Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-18 : 08:51:12
|
Del writes "I'm currently running the following dynamic SQL to delete a large number of records from a table that is updated on a daily basis, thus the number of rows to be deleted also varies:
select
'DELETE FROM schema.tbale_A WHERE x = '||x||';' FROM
schema.table_a WHERE x IN
(SELECT DISTINCT(x) FROM schema.table_b
WHERE TRUNC(DT_ENTRY) = trunc(sysdate)-1);
How can I ammend this/create one so that chunks of rows are being deleted in one go e.g. 100 at a time. I tried the following PL/SQL but this is in effect the same as the above:
================================================================
declare
cursor c1 is
select x from schema.table_a WHERE x IN
(SELECT DISTINCT(x) FROM schema.table_b
WHERE TRUNC(DT_ENTRY) = trunc(sysdate)-1);
tot_rows number;
icount number := 0;
begin
open c1;
loop
fetch c1 into tot_rows;
exit when c1%notfound;
if icount > 99 then
commit;
icount := 0;
end if;
dbms_output.put_line('DELETE FROM schema.table_a WHERE x = '||tot_rows||';');
icount := icount + 1;
END LOOP;
close c1;
end;
===============================================================
(Using ORACLE 8i)
Thanks in advance,
Del." |
|
|
|
|
|
Oracle: Dynamic SQL delete