Delete Query

[dmoney]

Please Help - I have searched the posts and have found some ideas, but
nothing seems to work. Here is the sql statement

Error 3086 resolutions do not apply as far as i can determine

DELETE DISTINCTROW DistributedAnalyst.*, DistributedByMaterial.Material
FROM DistributedAnalyst LEFT JOIN DistributedByMaterial ON
DistributedAnalyst.Material = DistributedByMaterial.Material
WHERE (((DistributedByMaterial.Material) Is Null));

Thanks in advance

 

[John W. Vinson]

Please Help - I have searched the posts and have found some ideas, but
nothing seems to work. Here is the sql statement

Error 3086 resolutions do not apply as far as i can determine

DELETE DISTINCTROW DistributedAnalyst.*, DistributedByMaterial.Material
FROM DistributedAnalyst LEFT JOIN DistributedByMaterial ON
DistributedAnalyst.Material = DistributedByMaterial.Material
WHERE (((DistributedByMaterial.Material) Is Null));

Thanks in advance

I presume you just want to delte records from DistributedAnalyst, and you're
using the material just for joining? If so remove it from the DELETE clause
and just leave it in the JOIN and the criteria:

DELETE DISTINCTROW DistributedAnalyst.*
FROM DistributedAnalyst LEFT JOIN DistributedByMaterial ON
DistributedAnalyst.Material = DistributedByMaterial.Material
WHERE (((DistributedByMaterial.Material) Is Null));

 

[dmoney]

your assumption was correct but unfortunately the modification produced the
same results. I did forget to mention that if i select view from design
mode, i can see the results of the query but if i try and run it, i still get
the error. Thank You.

 

[John Spencer]

Try rewriting the delete query

DELETE DistributedAnalyst.*
FROM DistributedAnalyst
WHERE NOT EXISTS
(SELECT *
FROM DistributedByMaterial
WHERE Material = DistributedAnalyst.Material )


OR try this rewrite which may be faster

DELETE *
FROM DistributedAnalyst
WHERE DistributedAnalyst.Material IN
(SELECT DistributedAnalyst.Material
FROM DistributedAnalyst LEFT JOIN DistributedByMaterial
ON DistributedAnalyst.Material = DistributedByMaterial.Material
WHERE DistributedByMaterial.Material Is Null)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[dmoney]

Thank You very much this one worked so much faster than the workaround I was
using. Appreciate it.

DELETE *
FROM DistributedAnalyst
WHERE DistributedAnalyst.Material IN
(SELECT DistributedAnalyst.Material
FROM DistributedAnalyst LEFT JOIN DistributedByMaterial