Delete Duplicate Rows

It's easy to delete duplicate rows in Oracle using cursor and rownum. Here shows an example.

Create table and insert some duplicate rows.

CREATE TABLE DUP_TEST
(
COMPANY_ID VARCHAR(8),
COMPANY_NAME VARCHAR(80),
ADDRESS VARCHAR(80)
);

-- Create test data, dup by company id
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('1', 'Company One', 'Address1');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address2');
INSERT INTO DUP_TEST VALUES ('2', 'Company Two', 'Address');
INSERT INTO DUP_TEST VALUES ('3', 'Company Three', 'Address3');

The duplicates are with company id 1 and 2. Let delete the duplicates.

set serveroutput on
declare
  rows_deleted integer := 0;
begin
  for cur in ( select company_id, count(*) cnt from DUP_TEST
               group by company_id having count(*) > 1 )
  loop
    delete from DUP_TEST
    where company_id = cur.company_id and rownum < cur.cnt;
    rows_deleted := rows_deleted + SQL%ROWCOUNT;
  end loop;
  dbms_output.put_line( 'records deleted: '  rows_deleted );
end;
/

Now show the result after deleting.

SQL> select * from dup_test;
COMPANY_ COMPANY_NAME ADDRESS
-------- -------------------- --------------------
1 Company One Address1
2 Company Two Address
3 Company Three Address3

It's a little bit diffcult to delete duplicate rows in SQL Server. I'll show one solution using a cursor. Consider the performance when you have large amount of data to search or delete.

declare cur cursor for select COMPANY_ID from DUP_TEST
declare @CompanyId int

open cur
fetch cur into @CompanyId

while @@fetch_status = 0
begin
if ( select count(*) from DUP_TEST where COMPANY_ID = @CompanyId ) > 1
begin
delete from DUP_TEST where current of cur
end
fetch cur into @CompanyId
end
close cur
deallocate cur
go

It goes through the full table and search duplicates for each row, if found, delete the current of cursor.

Multiply records

In one of my task, I needed to multiply some records in a table, so that they can have more chances to be picked up in a random draw. I did it in SQL Server, so I'll show it in SQL. It's similar in Oracle.
What I need to do is to Cartesian join the target records with a table with a certain number of records.

Create a test table and insert some records.

create table test_multiply( id int, type varchar(10), comments varchar(100) )
go
insert into test_multiply values ( 1, 'single', 'This row will show only once' )
insert into test_multiply values ( 2, 'times 10', 'This row will repeat 10 times' )
go

Now we're to multiply the second record 10 times, we join the records with a table contains 9 records.

insert into test_multiply
select a.* from test_multiply a
inner join ( select top 9 id from sysobjects ) x on 1=1
where a.type = 'times 10'
go
(9 rows affected)

Check the result.

select id, type, count(*) cnt from test_multiply group by id, type
go
id type cnt
----------- ---------- -----------
1 single 1
2 times 10 10

Similar in Oracle.

insert into test_multiply
select a.* from test_multiply a
inner join ( select 1 from all_objects where rownum < 1="1" type =" 'times">