Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How can I delete Duplicate records from a Database Table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: How can I delete Duplicate records from a Database Table
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    Post subject: Deleting duplicates
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us