[AccessD] A2000: DELETE query with JOIN

    You are most welcome Steve!

A.D.Tejpal
--------------

  ----- Original Message ----- 
  From: Steve Erbach 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, February 16, 2005 01:34
  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)));
  > =====================================