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
annanagarlt

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..

Cheers

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

Specialist


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.
Quote:
/* 1. Query heading */
SELECT
* /* Remember to never use SELECT * and write out the field names */
FROM
/* 2. Table with duplicates */
SHIPMENT_ORDERS
WHERE
EXISTS (
SELECT
NULL
FROM
/* 3. Table with duplicates, with an alias */
SHIPMENT_ORDERS b
WHERE
/* 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]
GROUP BY
/* 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]
HAVING
/* 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
johore
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