[AccessD] A2000: DELETE query with JOIN

Hi Steve

Oops, my fantasy stopped at the missing unique key ...
However, the dirty trick as, as you mention, to concatenate the fields
to a pseudo unique key:

  SELECT
    CAS1 & "_" & CAS2 & "_" & CAS3 & "_" & CAS4 AS CAS

Not nice but it may work.

/gustav

PS: This message will self-erase.

>>> erbachs at gmail.com 14-02-2005 18:57:42 >>>
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?