Please Help: Help removing all but the FIRST of duplicate records

wgre0111 · #1 (**permalink**) 08-19-09, 16:00

Greetings,

I am trying to remove duplicate records from a table but preserving one of them using temp tables/etc. Pretty much removing duplicates but leaving one of the identical sets.

Example DATA would like like:

col1, col2, col3
20, 1090, bad
22, 1090, bad
25, 1090, bad
28, 1091, good
2350, 2444, bad
2353, 2444, bad
2370, 2444, bad
3000, 5000, good
3005, 5001, good

AFTER the DELETE sql (which I need help with)
example data (based off of above) should look like

col1, [B]col2[/ col3
20, 1090, bad
28, 1091, good
2350, 2444, bad
3000, 5000, good
3005, 50001, good

Please notice that one of each of the sets that had duplicates remains.

The records the new sql would need to focus on would be records on col2 With counts(col2) >1 should not have two col2's with data like 1090. All but one of them would need to be deleted. Doesnt matter which one is left.

Please help!

wgre0111 · #2 (**permalink**) 08-19-09, 17:26

Something I would like to try is building a temp table and just comparing original table to the temp table... Dont know how to do this in informix though.... Here is a sketch.

delete Table1
from origtable Table1, temp_table Table2
where Table1.col2 = Table2.col2
and Table1.col1 > table2.col2

ceinma · #3 (**permalink**) 08-20-09, 08:39

Code:

create temp table tp01 (f1 smallint, f2 smallint, desc char(5));
Temporary table created.                                        

insert into tp01 values(20, 1090, "bad");
1 row(s) inserted.                       
insert into tp01 values(22, 1090, "bad");
1 row(s) inserted.                       
insert into tp01 values(25, 1090, "bad");
1 row(s) inserted.                       
insert into tp01 values(28, 1091, "good");
1 row(s) inserted.                        
insert into tp01 values(2350, 2444, "bad");
1 row(s) inserted.                         
insert into tp01 values(2353, 2444, "bad");
1 row(s) inserted.                         
insert into tp01 values(2370, 2444, "bad");
1 row(s) inserted.                         
insert into tp01 values(3000, 5000, "good");
1 row(s) inserted.                          
insert into tp01 values(3005, 5001, "good");
1 row(s) inserted.

select f2,max(rowid) rowid_ok from tp01
group by 1
into temp tp02;
5 row(s) retrieved into temp table.

delete from tp01
where rowid not in ( select rowid_ok from tp02);
4 row(s) deleted.

select * from tp01;

    f1     f2 desc

    25   1090 bad
    28   1091 good
  2370   2444 bad
  3000   5000 good
  3005   5001 good
5 row(s) retrieved.

dav1mo · #4 (**permalink**) 09-02-09, 16:52

how about select distinct col1,col2,col3 from your_table and then load that data?
Dave

Internet Services

Web Development

Digital Marketing

Consumer Tech