Board index » delphi » deleting duplicates from unindexed table

deleting duplicates from unindexed table

deleting duplicates from unindexed paradox table
How can this be done using sql?
is there a simpler way?
 

Re:deleting duplicates from unindexed table


Hi Richard,

A nice problem (why don't you use an unique index?).
What you need is a temp. table.
Create the temp. table like your table + a number field.

Insert the duplicates into the temp. table:
INSERT INTO yourTempTable (field1, field2, .... , fieldLast, recCound)
SELECT field1, field2, .... , fieldLast, Cound(*) AS recCound
FROM yourTable
GROUP BY field1, field2, .... , fieldLast
HAVING recCount > 1;

So you get all duplicate records.
Delete the records in your table with:
DELETE FROM yourTable
WHERE keyField IN (SELECT keyField FROM yourTempTable);

Insert the unique records:
INSERT INTO yourTempTable (field1, field2, .... , fieldLast)
SELECT field1, field2, .... , fieldLast FROM yourTable;

Drop the temp. table:
DROP TABLE yourTempTable;

Till now, I havn't wound a better way to do this.

hope i could help

    Markus

Richard Caruana schrieb:

Quote
> deleting duplicates from unindexed paradox table
> How can this be done using sql?
> is there a simpler way?

Other Threads