Deleting Duplicate Rows

It is not very common to delete duplicate rows but when you do then it has to be quick.

Here is an example of finding duplicate rows and then script to delete them.

To find the Duplicate rows

select article,art_version,medasset,med_version,count(*) from articleasses
group by article,art_version,medasset,med_version
 having count(*) >1

Delete Duplicate Rows

Once you are happy with the results of above query and backup the table then use this procedure to delete duplicate rows.

declare
v_rowid     varchar2(100);
i_count     number(3);
cursor c_dup
is
select medasset,med_version ,count(*) from medasset
group by medasset,med_version
having count(*) >1;
begin
    i_count := 0;
    i_count := 0;
    for r_dup in c_dup loop
        select max(rowid)
        into v_rowid
        from medasset
        where medasset = r_dup.medasset
 and med_version = r_dup.med_version;
        delete from medasset
        where medasset = r_dup.medasset
  and med_version=r_dup.med_version
        and rowid = v_rowid;
        i_count := i_count + 1;
        dbms_output.put_line('Record = '||i_count);
    end loop;
 end;
/

Check the results again.