How do we delete duplicate row in a table?



IBM's flagship relational database management system

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

Postby swd » Fri Jul 24, 2009 1:47 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

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

Postby RajendraPrabhuS » Fri Jul 24, 2009 2:04 pm

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.
RajendraPrabhuS
 
Posts: 33
Joined: Mon Jul 20, 2009 4:54 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby swd » Fri Jul 24, 2009 2:13 pm

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.
User avatar
swd
 
Posts: 109
Joined: Wed Feb 18, 2009 9:18 pm
Location: UK
Has thanked: 0 time
Been thanked: 0 time

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

Postby RajendraPrabhuS » Fri Jul 24, 2009 2:17 pm

Thanks Steve... Will consider the TIMESTAMP too...
RajendraPrabhuS
 
Posts: 33
Joined: Mon Jul 20, 2009 4:54 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby GuyC » Tue Aug 11, 2009 4:54 pm

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
;
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

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

Postby dick scherrer » Tue Aug 11, 2009 11:25 pm

Hello,

If this is a high-volume table, this would not be a good alternative. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby GuyC » Wed Aug 12, 2009 12:45 pm

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.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

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

Postby dick scherrer » Thu Aug 13, 2009 1:18 am

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. . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

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

Postby raghucheepinapi » Wed Sep 16, 2009 8:59 pm

Try this :

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

Raghu.
raghucheepinapi
 
Posts: 3
Joined: Thu Aug 27, 2009 12:35 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby GuyC » Fri Sep 18, 2009 12:15 pm

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.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post