Page 2 of 2

Re: How do we delete duplicate row in a table?

PostPosted: Fri Jul 24, 2009 1:47 pm
by swd
Yes, you can use the DB2 DSNUTILB program and perform an UNLOAD of the table. This will create the unload file and the PUNCH, or Statement file, that you use to reload the data back into the table, again using DSNUTILB. So this can be achieved by using utility programs DSNUTILB and DFSORT.

I'd speak to your DBA about doing the UNLOAD and LOAD REPLACE as you may need to experiment with this a bit if you have not used the DB2 unload and load before.

Re: How do we delete duplicate row in a table?

PostPosted: Fri Jul 24, 2009 2:04 pm
by RajendraPrabhuS
This is midbolwing Steve. I will use this utility to delete the duplicate records.... I am soo greatful to receive this information from you Steve, Thanks soo much.

Re: How do we delete duplicate row in a table?

PostPosted: Fri Jul 24, 2009 2:13 pm
by swd
You're welcome.

I must just say though that this would not be a recommended way of doing this. Perhaps consider adding a DB2 TIMESTAMP field. You could then delete the duplicate rows using the TIMESTAMP field.

Just a thought.

Re: How do we delete duplicate row in a table?

PostPosted: Fri Jul 24, 2009 2:17 pm
by RajendraPrabhuS
Thanks Steve... Will consider the TIMESTAMP too...

Re: How do we delete duplicate row in a table?

PostPosted: Tue Aug 11, 2009 4:54 pm
by GuyC
create temp_table like orig_table
;
insert into temp_table (select col1,col2,col3,col4 from orig_table group by col1,col2,col3,col4)
;
delete from orig_table
;
insert into orig_table (select * from temp_table)
;
drop table temp_table
;

Re: How do we delete duplicate row in a table?

PostPosted: Tue Aug 11, 2009 11:25 pm
by dick scherrer
Hello,

If this is a high-volume table, this would not be a good alternative. . .

Re: How do we delete duplicate row in a table?

PostPosted: Wed Aug 12, 2009 12:45 pm
by GuyC
True, but high volume table wasn't the question
RajendraPrabhuS wrote:Actually it a small table and you are correct i have to do it from SPUFI...


So the answer does exactly what was asked.

Re: How do we delete duplicate row in a table?

PostPosted: Thu Aug 13, 2009 1:18 am
by dick scherrer
Hello,

So the answer does exactly what was asked.
Yup, it does. IMHO another of the things that can be done that usually should not be done. . .

On well-managed/administered systems, data is not deleted (or otherwise modified) without proper footprints.

I've been some places where doing something like this in Production was grounds for termination. . .

If this is some little set of test data that has not been properly controlled to prevent duplicates, i've no problem with a quick spufi fix. I'd strongly suggest that the production system be made "duplicate-proof". Wth the current situation, how would the proper duplicate be kept and the other(s) discarded? If this is just test data, it may not matter. If there is different content in the duplicates it may be less than trivial for "real" data. . .

Re: How do we delete duplicate row in a table?

PostPosted: Wed Sep 16, 2009 8:59 pm
by raghucheepinapi
Try this :

DELETE FROM TABLE ABC
WHERE COLUMN =
(SELECT COLUMN FROM TABLE ABC
GROUP BY COLUMN
HAVING COUNT(*) > 1);

Raghu.

Re: How do we delete duplicate row in a table?

PostPosted: Fri Sep 18, 2009 12:15 pm
by GuyC
raghucheepinapi wrote:Try this :

DELETE FROM TABLE ABC
WHERE COLUMN =
(SELECT COLUMN FROM TABLE ABC
GROUP BY COLUMN
HAVING COUNT(*) > 1);

Raghu.

This will delete both rows. so if 1001 is duplicate after this you will have 0 rows with 1001 instead of the desired 1.