Could not delete from specified tables.

[Joseph]

Hi,

I'm trying to delete duplicate records from 1 main table that i have.
It is Access DB is linked table on the SQL server. I can manually
delete the record but I cannot do it by query.

Here is the SQL query:

DELETE DISTINCTROW [dbo_CP Table 1].*, Distinct.[Identifier Field]
FROM [dbo_CP Table 1] INNER JOIN [Find duplicates for dbo_CP Table 1] AS
[Distinct] ON [dbo_CP Table 1].Identifier = Distinct.[Identifier Field]
WHERE (((Distinct.[Identifier Field])=[Distinct].[Identifier Field]));

 

[[MVP] S.Clark]

This typically means that you do not have a unique index on the table of
which the deletes will occur.

 

[Joseph]

Well that's the thing. It "did" have a primary key but when it was
imported it was imported with duplicate records. So i'm trying to
delete these duplicate records i get the error. I've tried importing
all the records locally and try to run the query but still a no go. How
can i do this without manually doing so?

TIA

 

[John Spencer]

DELETE DISTINCTROW [dbo_CP Table 1].*, Distinct.[Identifier Field]
FROM [dbo_CP Table 1] INNER JOIN [Find duplicates for dbo_CP Table 1] AS
[Distinct] ON [dbo_CP Table 1].Identifier = Distinct.[Identifier Field]
WHERE (((Distinct.[Identifier Field])=[Distinct].[Identifier Field]));

You can only DELETE from one table at a time. You have two "Tables"
referenced in Delete clause. So try

DELETE DISTINCTROW [dbo_CP Table 1].*
FROM [dbo_CP Table 1] INNER JOIN [Find duplicates for dbo_CP Table 1] AS
[Distinct] ON [dbo_CP Table 1].Identifier = Distinct.[Identifier Field]
WHERE (((Distinct.[Identifier Field])=[Distinct].[Identifier Field]));

OR try this variation

DELETE DISTINCTROW [dbo_CP Table 1].*
FROM [dbo_CP Table 1]
WHERE [dbo_CP Table 1].Identifier IN (
SELECT [Identifier Field]
FROM [Find duplicates for dbo_CP Table 1])