Re: Delete query constrained by join
- From: "[MVP] S.Clark" <steve.clark_NOSPAM@xxxxxxxxxx>
- Date: Tue, 30 Aug 2005 09:44:12 -0400
Both tables need to have a unique index or primary key.
--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
Consulting@xxxxxxxxxx
www.fmsinc.com/consulting
"sean.howard" <sean.howard.1ujs2p@xxxxxxxxxxxxxxxxxx> wrote in message
news:sean.howard.1ujs2p@xxxxxxxxxxxxxxxxxxxxx
>
> I want to delete records from a table that are in a list represented by
> 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)=[fix
> VTMM-5].[productID]) AND
> ((dbo_Products_CategoriesAssoc.CategoryID)=[fix
> 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 JOIN
> (dbo_Products_CategoriesAssoc INNER JOIN dbo_Categories ON
> dbo_Products_CategoriesAssoc.CategoryID = dbo_Categories.CategoryID) ON
> dbo_Products_AttributesAssoc.ProductID =
> dbo_Products_CategoriesAssoc.ProductID) INNER JOIN dbo_Attributes ON
> dbo_Products_AttributesAssoc.AttributeID = dbo_Attributes.AttributeID
> WHERE (((dbo_Attributes.Attribute) Not Like "*all*") AND
> ((dbo_Categories.Category) Like "*all*") AND
> ((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 from
> the specified table." I can't figure out why this doesn't work.
>
>
> --
> sean.howard
> ------------------------------------------------------------------------
> sean.howard's Profile: http://www.msusenet.com/member.php?userid=4759
> View this thread: http://www.msusenet.com/t-1871019383
>
.
- References:
- Delete query constrained by join
- From: sean . howard
- Delete query constrained by join
- Prev by Date: Re: Addition calculation across the record
- Next by Date: Re: same parameter is used multiple times in one qry
- Previous by thread: Delete query constrained by join
- Next by thread: Re: Delete query constrained by join
- Index(es):
Relevant Pages
|
Loading
