[AccessD] A2000: DELETE query with JOIN

Steve Erbach erbachs at gmail.com
Mon Feb 14 11:34:44 CST 2005


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



More information about the AccessD mailing list