Page 1 of 2

How do we delete duplicate row in a table?

PostPosted: Mon Jul 20, 2009 5:16 pm
by RajendraPrabhuS
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.

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

PostPosted: Mon Jul 20, 2009 8:34 pm
by vinter
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.

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

PostPosted: Mon Jul 20, 2009 8:39 pm
by vinter
... 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.

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

PostPosted: Tue Jul 21, 2009 12:28 pm
by RajendraPrabhuS
Actually it a small table and you are correct i have to do it from SPUFI...

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

PostPosted: Wed Jul 22, 2009 4:41 pm
by vinter
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.

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

PostPosted: Thu Jul 23, 2009 2:42 am
by dick scherrer
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.

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

PostPosted: Thu Jul 23, 2009 5:47 pm
by swd
Why not add a primary key, then the duplicates won't get on in the first place?

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

PostPosted: Thu Jul 23, 2009 6:25 pm
by RajendraPrabhuS
The requirnment was like that...... We need to find a way to delete a duplicte row....

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

PostPosted: Thu Jul 23, 2009 8:15 pm
by swd
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

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

PostPosted: Fri Jul 24, 2009 9:01 am
by RajendraPrabhuS
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??