How do we delete duplicate row in a table?



IBM's flagship relational database management system

How do we delete duplicate row in a table?

Postby RajendraPrabhuS » Mon Jul 20, 2009 5:16 pm

I just want to know how do we delete duplicate rows in a table. There is no Primary key is defined in the table.
For eg.,
1001 XXXX 25 FINANCE
1002 YYYY 27 TESTING
1003 ZZZZ 28 FINANCE
1004 AAAA 29 HR
1001 XXXX 25 FINANCE
1005 BBBB 30 HR
1006 CCCC 27 TESTING

From the above the record 1001 was repeated twice and I Just want to delete only one row. And another copy to be remain in the table.
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 vinter » Mon Jul 20, 2009 8:34 pm

Is it a very large table and does it have to be done many times, so performance is an issue?

My solution would depend on this.
vinter
 
Posts: 7
Joined: Mon Jul 20, 2009 8:27 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby vinter » Mon Jul 20, 2009 8:39 pm

... oh, and do you have access to a programming language, or do you have to do it from something like Spufi? In the latter case, I have no clue.
vinter
 
Posts: 7
Joined: Mon Jul 20, 2009 8:27 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby RajendraPrabhuS » Tue Jul 21, 2009 12:28 pm

Actually it a small table and you are correct i have to do it from SPUFI...
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 vinter » Wed Jul 22, 2009 4:41 pm

RajendraPrabhuS wrote:Actually it a small table and you are correct i have to do it from SPUFI...

Hm, then I don't know. Sorry.
vinter
 
Posts: 7
Joined: Mon Jul 20, 2009 8:27 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby dick scherrer » Thu Jul 23, 2009 2:42 am

Hello,

i have to do it from SPUFI...
A completely bad use of spufi (unless this is just some test data). Any "real" update should have an audit trail so that next week or next month people can see which were the discarded rows.
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 swd » Thu Jul 23, 2009 5:47 pm

Why not add a primary key, then the duplicates won't get on in the first place?
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 » Thu Jul 23, 2009 6:25 pm

The requirnment was like that...... We need to find a way to delete a duplicte row....
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 » Thu Jul 23, 2009 8:15 pm

Well, you could unload the table, sort the unload file to remove duplicates (SUM FIELDS=NONE) and then LOAD REPLACE the table with the sortout file. That would work, but it's not ideal.

If you want to delete using SPUFI then you need to add some unique field to the table, perhaps an incremented number or something like that.

Steve
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 9:01 am

Thanks Steve. Is there any way to Extract the table data into a file without using Application Program like COBOL etc.,? Is there anything we can do with SPUFI/QMF to Extract table content into a file??
RajendraPrabhuS
 
Posts: 33
Joined: Mon Jul 20, 2009 4:54 pm
Has thanked: 0 time
Been thanked: 0 time

Next

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post