Delete Duplicate Query Freeze

[Auto]

hi, i am having a table with 854977 records in it, each time an append query puts in data into the table, i must run a delete duplicate query against it, so duplicate records are removed, i build a query which deletes duplicate records, and it works for a table that has 30,000 records like a charm, but when i run it against my table which has 854,977 records its freezing and hangs down, ahat can i do for that?

here is how my table looks like, it has 4 columns

ID Image ITEM FilePath

now a duplicate record is considered ONLY if all 3 fields are the same, ID will not be the same its autonumber column

here is my query that i use

DELETE Orders.*FROM Orders
WHERE ((((SELECT COUNT (*) FROM Orders AS L
WHERE (L.Image & L.ITEM & L.FilePath
= Orders.Image & Orders.ITEM & Orders.FilePath)
AND (L.ID <= Orders.ID)))>1));

then i tried another one, and it also hangs here it is

DELETE *FROM Orders


WHERE Orders.ID NOT IN
(Select MAX(ID)
FROM Orders
GROUP BY Orders.Image, Orders.ITEM, Orders.FilePath
);

[AlexHedley]

Why not run the checks before the append query so you don't add duplicates then won't need the DELETE.

[Auto]

Why not run the checks before the append query so you don't add duplicates then won't need the DELETE.

thats my lesson for the future, but now i have duplicate data, how can i get rid of it?

[AlexHedley]

Is the Table Indexed?

If you run it just as a SELECT Query does it also fail?

[Auto]

Is the Table Indexed?

yes, ID is indexed

If you run it just as a SELECT Query does it also fail?

it starts to give out first 100 results, then it freeze for few seconds, then it gives 100 more results, and so on...

[orange]

Do you have a Primary Key on this table?
To get rid of duplicates you could

-create another table with the same fields
-identify the field( or combo of fields) that makes each record unique
-create a query to insert records from your existing table into the new table (with the primary key defined)
-run the query

Not all records will be moved to the new table.Those records are the ones that violate uniqueness (are the duplicates/replicates)

The fundamental issue with your set up is "failure to uniquely define records", or in other words, not assigning a Primary Key to your table.

Good luck.

[Auto]

Do you have a Primary Key on this table?
To get rid of duplicates you could

-create another table with the same fields
-identify the field( or combo of fields) that makes each record unique
-create a query to insert records from your existing table into the new table (with the primary key defined)
-run the query

Not all records will be moved to the new table.Those records are the ones that violate uniqueness (are the duplicates/replicates)

The fundamental issue with your set up is "failure to uniquely define records", or in other words, not assigning a Primary Key to your table.

Good luck.

thanks Orange, for your reply.

so i created a new table called OrdersT
Fields are like this

ID AutoNumber
then there is an index with the name Image, and it has 3 fields in the index, looks like this

Index Name	Field Name	Sort Order
Image	Image	Ascending
	ITEM	Ascending
	FilePath	Ascending

primary Yes
Unique Yes
Ignore Nulls No

and here is the SQL to push in the records

INSERT INTO OrdersT ( [Image], ITEM, FilePath )SELECT Orders.Image, Orders.ITEM, Orders.FilePath
FROM Orders;

and it pushed in 32,717 records,
so want to see if its acurate, so i made a select distinct query on the Orders table to see if i get same results
and i got 32,994
thats a difference of 277 records

where am i wrong?

here is the select distinct query

SELECT DISTINCT Orders.Image, Orders.ITEM, Orders.FilePathFROM Orders
Group By Orders.Image, Orders.ITEM, Orders.FilePath;

what am i doing wrong?

[orange]

Did you get a message that xxxx records were not added because of Key Violations?
What field(s) did you use for you Primary Key?

[Auto]

Did you get a message that xxxx records were not added because of Key Violations?

yes

What field(s) did you use for you Primary Key?

Index Image holds all 3 fields, and its set for primary key, so all 3 are primary

[orange]

Try

SELECT Count(*) From  YournewTableName

and see what the number is.

If your table, and your PK and a field are all named "Image" that could be an issue.

[Auto]

Try

SELECT Count(*) From  YournewTableName

and see what the number is.

32717

If your table, and your PK and a field are all named "Image" that could be an issue

i changed it, and i still get same results

[orange]

Can you zip your database and post it?

[Auto]

i am having private info from ppl in it, so i cant do it, sorry.

[Auto]

ok, i got where the problem is, thats a tough one.
i made a query to check what are the 277 records that are in the distinct query but not in the OrdersT table, and i got the 277 records, so i looked into it, and the problem is this

they all have a null value in one of the fields, and ignore nulls is set to "no", i cant set it yes i get an error
"you cant set the ignoreNulls property of a primary key to yes"

so what are my options here?
is the select distinct query the most reliable? it holds the records even it has nulls, but how can i assure i am not loosing any record?

[orange]

A Primary Key (even a composite Primary key) uniquely identifies all rows in a table -- so it can not contain nulls.
A unique index can contain nulls, but it doesn't uniquely identify all records in a table.

Why are some fields NULL if they are required for the Primary Key?