Re: removing duplicate records
> Hi, is there any clean way of deleting the duplicate records in a
> table using only ISQL?
The following is an example sql script to delete duplicate records.
However it may be faster to unload and drop the table, run sort -u
on the unloaded date to remove duplicate lines. Then recreate and
reload the table.
------------------------------------------------------------------
{ create a table and insert some data with duplicates }
create table t1 ( col1 integer, col2 char(5));
insert into t1 values ( 1, "1AAAA");
insert into t1 values ( 2, "2AAAA");
insert into t1 values ( 2, "2AAAA"); { duplicate }
insert into t1 values ( 3, "3AAAA");
insert into t1 values ( 4, "4AAAA");
insert into t1 values ( 4, "4AAAA"); { duplicate }
insert into t1 values ( 4, "4AAAA"); { duplicate }
{ 1. select all rows with duplicate records }
select col1, col2, count(*) duplicate_cnt
from t1
group by col1, col2 having count(*) > 1
into temp temp_dup;
{ 2. select the rowid's of duplicate records }
select t1.rowid recno, t1.col1, t1.col2
from t1, temp_dup
where t1.col1 = temp_dup.col1
and t1.col2 = temp_dup.col2
into temp temp_dup2;
{ 3. select the first ( min ) rowid for each duplicate record }
select min( recno) recno, temp_dup2.col1, temp_dup2.col2
from temp_dup2
group by col1, col2
into temp temp_dup_save;
{ display rowid's to be deleted - this step shows what will be deleted and is
not really nessary }
select t1.rowid, t1.*
from t1
where rowid in ( select recno from temp_dup2
where recno not in ( select recno from temp_dup_save ));
{ 4. delete rowid's to delete }
delete from t1
where rowid in ( select recno from temp_dup2
where recno not in ( select recno from temp_dup_save ));------------------------------------------------------------------
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Providing Informix Database Tools and Consulting #
#############################################################################