[AccessD] A2000: DELETE query with JOIN

Hi Steve

I usually get away with creating a that selects the ID of those records
not to be deleted.
Then you can delete from the main table:

  DELETE *
  FROM tblTable
  WHERE
    ID NOT IN (SELECT ID FROM qdyNoToBeDeleted;)

/gustav

>>> erbachs at gmail.com 14-02-2005 18:34:44 >>>
Dear Group,

Creating a DELETE query in which records from one table are deleted
based on the contents of another table is very straightforward when
there's only one linking field. My problem is with a JOIN that
requires four linking fields. Here's what I'm trying:

DELETE T1.* 
FROM T1 
INNER JOIN T2 
ON (T1.CAS4 = T2.CAS4) 
AND (T1.CAS3 = T2.CAS3) 
AND (T1.CAS2 = T2.CAS2) 
AND (T1.CAS1 = T2.CAS1);

I've also tried it this way:

DELETE T1.* 
FROM T1 
INNER JOIN T2 
ON (T1.CAS4 = T2.CAS4) 
AND (T1.CAS3 = T2.CAS3) 
AND (T1.CAS2 = T2.CAS2) 
AND (T1.CAS1 = T2.CAS1);
WHERE (
   (([T1].[CAS1])=[T2].[CAS1]) 
   AND (([T1].[CAS2])=[T2].[CAS2]) 
   AND (([T1].[CAS3])=[T2].[CAS3]) 
   AND (([T1].[CAS4])=[T2].[CAS4]));

Both ways I get the message:

Could not delete from specified tables.

But when I View the results of the query before I try to Run it, I see
that the proper records from T1 have been selected for deleting. That
is, I see the four CASx fields with the numbers that are supposed to
be there. There are 936 records in T1 and 648 in T2 and those 648 are
supposed to be deleted from T1. But the Delete won't go forward.

What am I doing wrong?

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com 
Security Page: www.swerbach.com/security