Delete Query Problem

[Bonnie]

I relating two tables to find dups (if exportTable has the same records as
Master I want to deleted them from the ExportTable). I get the error:
"Could not delete from specified table"

Here's the SQL. Any ideas? Thanks.

DELETE ExportTable.VIN, ExportTable.*
FROM ExportTable LEFT JOIN Master ON (ExportTable.[Report Type] =
Master.Report_Type) AND (ExportTable.[Date of Loss] = Master.Date_of_Loss)
AND (ExportTable.VIN = Master.VIN)
WHERE ((Not (ExportTable.VIN) Is Null));

 

[Jerry Whittle]

You might, just might, get away with it on an INNER JOIN, but not a LEFT
JOIN. Either way it's usually best to do this with a sub-query.

STANDARD WARNING: Test the following on a copy of your database to make sure
it works properly.

DELETE ExportTable.*
FROM ExportTable
WHERE ExportTable.VIN IN (SELECT Master.VIN
FROM Master
WHERE ExportTable.[Report Type] = Master.Report_Type
AND ExportTable.[Date of Loss] = Master.Date_of_Loss
AND ExportTable.VIN = Master.VIN) ;

 

[Bonnie]

That did it Jerry. Thanks.

You might, just might, get away with it on an INNER JOIN, but not a LEFT
JOIN. Either way it's usually best to do this with a sub-query.

STANDARD WARNING: Test the following on a copy of your database to make sure
it works properly.

DELETE ExportTable.*
FROM ExportTable
WHERE ExportTable.VIN IN (SELECT Master.VIN
FROM Master
WHERE ExportTable.[Report Type] = Master.Report_Type
AND ExportTable.[Date of Loss] = Master.Date_of_Loss
AND ExportTable.VIN = Master.VIN) ;

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I relating two tables to find dups (if exportTable has the same records as
Master I want to deleted them from the ExportTable). I get the error:
"Could not delete from specified table"

Here's the SQL. Any ideas? Thanks.

DELETE ExportTable.VIN, ExportTable.*
FROM ExportTable LEFT JOIN Master ON (ExportTable.[Report Type] =
Master.Report_Type) AND (ExportTable.[Date of Loss] = Master.Date_of_Loss)
AND (ExportTable.VIN = Master.VIN)
WHERE ((Not (ExportTable.VIN) Is Null));