Home » RDBMS Server » Server Administration » Re: Remove Duplicate Items
Re: Remove Duplicate Items [message #370197] Wed, 05 April 2000 11:22
hmg
Messages: 40
Registered: March 1999
Member
<TT>

-- this example works in oracle 8.0.5

-- create table "exceptions"
drop table exceptions;
@e:\orant\rdbms80\admin\utlexcpt.sql

-- create table test
drop table test;
create table test (
   col1  number(10),
   col2  number(10),
   col3  varchar2(5)
);

-- insert rows
insert into test values ( 1, 1, 'AA' );
insert into test values ( 1, 2, 'AB' );
insert into test values ( 2, 1, 'BA' );
insert into test values ( 2, 1, 'BA' );
insert into test values ( 3, 1, 'CA' );
insert into test values ( 3, 2, 'CB' );
insert into test values ( 3, 2, 'CX' );
insert into test values ( 4, 1, 'DA' );
insert into test values ( 4, 2, 'DB' );
insert into test values ( 4, 3, 'DC' );
commit;

-- first attempt to add primary key on (col1,col2)
alter table test
   add primary key (col1,col2)
   exceptions into exceptions;

 
-- to view the bad rows
select * from test
   where rowid in
      (select row_id from exceptions);

      
-- to remove some rows that the primary key could be enabled
delete from test
   where rowid in
       (select min(rowid) from test
           group by col1, col2
           having count(*) > 1);

           
alter table test
   add primary key (col1,col2)
   exceptions into exceptions;

</TT> 
Previous Topic: log_archive_dest on network drive
Next Topic: Upgrade Oracle database
Goto Forum:
  


Current Time: Wed Oct 28 06:53:16 CDT 2020