| Author |
Message |
annanagarlt
New User
Joined: 31 Oct 2003
Posts: 5
|
|
|
|
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 |
|
 |
References
|
 Posted: Thu Nov 27, 2003 12:55 pm Post subject: Re: How can I delete Duplicate records from a Database Table |
|
|
|
|
Johore
Specialist
Joined: 12 Sep 2003
Posts: 20
|
|
|
|
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 |
|
|
|
|
