IBM Mainframe Forum Index
Log In
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register

How can I delete Duplicate records from a Database Table

IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message

New User

Joined: 31 Oct 2003
Posts: 5

PostPosted: Thu Nov 27, 2003 12:55 pm
Reply with quote

Hi all,
How can I delete Duplicate records from a Database Table. I don't need those records to be transferred to a new table...

I put it clear.. USING only 1 TABLE. looking forward for a quick reply..


Team AnnaNagar LT.
Back to top
View user's profile Send private message


Joined: 12 Sep 2003
Posts: 19

PostPosted: Fri Nov 28, 2003 9:50 am
Reply with quote

Hi ,

Here i have a query which displays the Duplicate records in a table. You can remove the duplicates by replacing SELECT * by DELETE.

The table is Shipment_orders and the primary keys are Shipment,purchase_order and item_sku.
/* 1. Query heading */
* /* Remember to never use SELECT * and write out the field names */
/* 2. Table with duplicates */
/* 3. Table with duplicates, with an alias */
/* 4. Join each field with *itself*. These are fields that could be Primary Keys */
b.[shipment] = SHIPMENT_ORDERS.[shipment]
AND b.[purchase_order] = SHIPMENT_ORDERS.[purchase_order]
AND b.[item_sku] = SHIPMENT_ORDERS.[item_sku]
/* 5. I must GROUP BY these fields because of the HAVING
clause and because these are the possible PK */
b.[shipment], b.[purchase_order], b.[item_sku]
/* 6. This is the determining factor. We can control our
output from here. In this case, we want to pick records
where the ID is less than the MAX ID */
SHIPMENT_ORDERS.[id] < MAX(b.[id])

Here we compare each field to its copy.The GROUP BY clause is used to ensure that the data is unique.
To remove all the duplicated from the table replace the SELECT * with DELETE FROM.

Hope this helps
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 1
No new posts Duplicate transid's declared using CEDA CICS 3
No new posts DSNTIAUL driven delete IBM Tools 0
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top