Delete all except 3

[rshivagami]

Hi all,

I need to write a PL/SQL code to delete all records except the first 3 for a particular field value in a table.
Does anyone know a fast way of doing it??

Shiva.

[smoothyc]

Here's just a really high-level example of a PL/SQL statement. It may not be exactly what you're looking for, but I hope it helps in some way.


CURSOR update_cur
IS
SELECT FIELD_A AND THE REST OF THE FIELDS
FROM
TABLE_01;

keep_record_counter number(1) := 0;
delete_counter number(5) := 0;

BEGIN
FOR update_rec IN update_cur
LOOP
If keep_record_counter = 3
then
delete the rest of the records
else
IF field_a = whatever
then
keep the record
add one to the keep_record_counter
else
delete the record
add one to the delete_counter
end if
end if;

If the delete_counter := 5000
THEN
do a commit
end if;
END LOOP;
END;

also see:
[url]http://dbasupport.com/oracle/faq/Detailed/325.shtml[/url]


[Edited by smoothyc on 02-28-2001 at 09:54 AM]