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 Duplicate data in PUT CONTAINER using... CICS 4
No new posts Capturing COBOL job and program names... All Other Mainframe Topics 2
No new posts how to keep unpaired records with REPRO. JCL & VSAM 9
No new posts Dataset size increase on adding 1 byt... DFSORT/ICETOOL 8
No new posts Delete file row if blanks in the firs... DFSORT/ICETOOL 5
Search our Forums:

Back to Top