[AccessD] A2000: DELETE query with JOIN

I thought in 2000 and later you HAD to have unique keys in order to run
a delete query using multiple tables.

Charlotte Foust


-----Original Message-----
From: Steve Erbach [mailto:erbachs at gmail.com] 
Sent: Tuesday, February 15, 2005 12:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2000: DELETE query with JOIN


A.D.,

Ah! Here is the crucial point! The tables I was working with were
actually working tables; that is, tables created from MakeTable queries.
Thus they did not have keys.

When I run the query on these non-keyed tables I get the error message
"Could not delete from specified tables." When I set the 4 CAS fields to
be keys in both T1 and T2, the DELETE query worked fine.

This is very interesting. This is the first instance I've run across
where a query works fine with keyed tables but not with unkeyed tables.

Thank you.

Steve Erbach
Neenah, WI

On Tue, 15 Feb 2005 22:03:04 +0530, A.D.Tejpal <adtp at touchtelindia.net>
wrote:
> Steve,
> 
>     You are aiming at deletion of such records in table T1 as match 
> those in table T2, based upon the combination of four fields (CAS1 to 
> CAS4). It is presumed that no relationship has been enforced between 
> the two tables. Only permissible relationship would have been one to 
> many (or one to one) between T2 and T1, and that would imply deletion 
> of all records in table T1.
> 
>     Both styles of delete query, as given below are found to work
successfully.
>     (a) Multiple Join of four fields (CAS1 to CAS4)
>     (b) No Join (uses In clause instead)
> 
>     For query (a) to work, it has to be ensured that primary key for 
> table T2 (combination of CAS1 to CAS4) is set correctly . For table 
> T1, setting of such primary key is not essential. However, there is no

> problem if it exists.
> 
>     For query (b) to work, status of primary key (covering the four 
> fields) in the two tables has no bearing.
> 
>     SQL for both cases is given below. Query (a) is expected to be 
> faster than that at (b). If required, a sample db demonstrating the 
> two queries, can be sent to you.
> 
> With best wishes,
> A.D.Tejpal
> --------------
> 
> (a) Delete Query - Multiple Join =====================================
> DELETE T1.*
> FROM T1 INNER JOIN T2 ON (T1.CAS1 = T2.CAS1) AND (T1.CAS2 = T2.CAS2)
AND (T1.CAS3 = T2.CAS3) AND (T1.CAS4 = T2.CAS4);
> =====================================
> 
> (b) Delete Query - No Join (Uses In clause instead) 
> =====================================
> DELETE T1.*
> FROM T1
> WHERE ((([CAS1] & [CAS2] & [CAS3] & [CAS4]) In (Select  [CAS1] & 
> [CAS2] & [CAS3] & [CAS4]  From T2))); 
> =====================================
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com