Delete query constrained by join

S

sean.howard

I want to delete records from a table that are in a list represented b
a subquery. Trying this in MS Access 2003 produced the following SQL:

DELETE dbo_Products_CategoriesAssoc.*
FROM dbo_Products_CategoriesAssoc, [Fix VTMM-5]
WHERE (((dbo_Products_CategoriesAssoc.ProductID)=[fi
VTMM-5].[productID]) AN
((dbo_Products_CategoriesAssoc.CategoryID)=[fi
VTMM-5].[categoryID]));

The [Fix VTMM-5] subquery is this:

SELECT dbo_Products_CategoriesAssoc.ProductID
dbo_Products_CategoriesAssoc.CategoryID, dbo_Attributes.Attribute
FROM (dbo_Products_AttributesAssoc INNER JOI
(dbo_Products_CategoriesAssoc INNER JOIN dbo_Categories O
dbo_Products_CategoriesAssoc.CategoryID = dbo_Categories.CategoryID) O
dbo_Products_AttributesAssoc.ProductID
dbo_Products_CategoriesAssoc.ProductID) INNER JOIN dbo_Attributes O
dbo_Products_AttributesAssoc.AttributeID = dbo_Attributes.AttributeID
WHERE (((dbo_Attributes.Attribute) Not Like "*all*") AN
((dbo_Categories.Category) Like "*all*") AN
((dbo_Products_AttributesAssoc.TypeID)=5))
ORDER BY dbo_Products_CategoriesAssoc.ProductID
dbo_Products_CategoriesAssoc.CategoryID;


Viewing the list of records produces the correct recordset. However
when I run this query, I get an error message: "Could not delete fro
the specified table." I can't figure out why this doesn't work
 
S

sean.howard

I got this to work in Visual Studio.Net. Simply combined the querie
using IN operators in WHERE clauses. You're very perceptive...Th
table dbo.Products_AttributesAssoc does not have a primary key.
suppose that is why it doesn't work in MS Access. I don't suppose yo
can tell me why it works in VS.net?

DELETE FROM dbo.Products_CategoriesAssoc
WHERE (ProductID IN
(SELECT
dbo.Products_AttributesAssoc.ProductID
FROM (dbo.Attributes INNER JOIN

dbo.Products_AttributesAssoc ON dbo.Attributes.AttributeID
dbo.Products_AttributesAssoc.AttributeID) INNER JOIN

dbo.Products_CategoriesAssoc ON dbo.Products_AttributesAssoc.ProductI
= dbo.Products_CategoriesAssoc.ProductID
WHERE
(((dbo.Products_AttributesAssoc.TypeID) = 5) AN
((dbo.Attributes.Attribute) NOT LIKE N'%ALL%')))) AND (CategoryID IN
(SELECT dbo.Categories.CategoryID
FROM dbo.Categories INNER JOIN

dbo.Products_CategoriesAssoc ON dbo.Categories.CategoryID
dbo.Products_CategoriesAssoc.CategoryID
WHERE (((dbo.Categories.Category) LIK
N'%all%')))
 
J

John Spencer (MVP)

Try the following variation.

DELETE DISTINCTROW dbo_Products_CategoriesAssoc.*
FROM dbo_Products_CategoriesAssoc INNER JOIN [Fix VTMM-5]
 
S

sean.howard

Thank you for your reply. I got the error message "Could not delet
from specified tables." I still don't understand why this won't wor
in Access when it worked in VS.net. I realize that the SQL is slightl
different, but still..
 
Top