[AccessD] A2000: DELETE query with JOIN

Gustav,

And, as I said, it's pretty straightforward when there's only one
linking field. But I've got four and that's where the trouble lies.
I've used the IN and NOT IN to good effect in the past...but they only
work with a single linking field.

Hmmm...perhaps if I create a single field by stringing together all
four of my links...

Steve Erbach


On Mon, 14 Feb 2005 18:52:17 +0100, Gustav Brock <Gustav at cactus.dk> wrote:
> 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
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
Regards,

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