[AccessD] A2000: DELETE query with JOIN

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)));
=====================================

  ----- Original Message ----- 
  From: Steve Erbach 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, February 15, 2005 01:27
  Subject: Re: [AccessD] A2000: DELETE query with JOIN


  Gustav,

  Yes, that's something I did try; that is, removing the JOINs and going with the WHERE clause only. No dice.

  I don't recall where that consitent/inconsistent updates thing is.

  Steve Erbach


  On Mon, 14 Feb 2005 20:29:31 +0100, Gustav Brock <Gustav at cactus.dk> wrote:
  > Hi Steve
  > 
  > I find it a pity that competition fades away.
  > 
  > By the way, couldn't you do it without the NOT IN (...) or temp table
  > by - in your original attempt - not using joins but a simple WHERE
  > S.CAS1<>T.CAS1 AND S.CAS2<>T.CAS2 AND etc.?
  > Also, did you try the consistent/inconsistent updates setting?
  > 
  > /gustav