Hi

1. I need to delete duplicate records from a table called "product".
2. The table should only keep "one" version of the record. ( the first is fine )
3. The duplicate should only delete if : "product_description" AND "product_code" both "Match"

How do i go about this using SQL?

I had a look in the forum but can't match/understand as to what I need to do.

Thanks

Kamran

kamran wrote:

<< 1. I need to delete duplicate records from a table called "product".
2. The table should only keep "one" version of the record. ( the first is fine )
3. The duplicate should only delete if : "product_description" AND "product_code" both "Match"

How do i go about this using SQL? >>

You'll need a script with DBISAM to do this.  But, quick question first: is there any unique identifying columns in the product table, apart from the product_code column ?

Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

Hi Tim

Yes there is a unique index called "product_internal_code".

Thanks

Kamran

kamran wrote:

<< 1. I need to delete duplicate records from a table called "product".
2. The table should only keep "one" version of the record. ( the first is fine )
3. The duplicate should only delete if : "product_description" AND "product_code" both "Match"

How do i go about this using SQL? >>

You'll need a script with DBISAM to do this.  But, quick question first: is there any unique identifying columns in the product table, apart from the product_code column ?

Tim Young
Elevate Software
www.elevatesoft.com

kamran,

<< Yes there is a unique index called "product_internal_code".>>

Okay, here's a solution.  I say "solution" because it's a) not the best, and relies on some DBISAM-specific grouping behavior, and b) may need some further optimization, depending upon the number of records involved.

SELECT Product_Internal_code, Product_Code,Product_description
INTO "memory\temp"
FROM product
GROUP BY 2,3
ORDER BY 1;

DELETE FROM product
INNER JOIN "memory\temp" ON temp.product_internal_code <> product.product_internal_code AND
temp.product_code=product.product_code AND temp.product_description=product.product_description;

Tim Young
Elevate Software
www.elevatesoft.com