jeffrey.demulling at usbank.com
jeffrey.demulling at usbank.com
Mon Feb 14 12:08:01 CST 2005
Have not tried this but it might work.
DELETE T1.*
FROM
(SELECT
T1.PrimaryKey
FROM
T1
JOIN T2
ON
(T1.CAS4 = T2.CAS4)
AND
(T1.CAS3 = T2.CAS3)
AND
(T1.CAS2 = T2.CAS2)
AND
(T1.CAS1 = T2.CAS1) AS tempQuery)
WHERE
tempQuery.PrimaryKey = T1.PrimaryKey
Jeffrey F. Demulling
Project Manager
U.S. Bank Corporate Trust Services
60 Livingston Avenue
EP-MN-WS3C
St. Paul, MN 55107-2292
Ph: 651-495-3925
Fax: 651-495-8103
email: jeffrey.demulling at usbank.com
"Steve Erbach"
<erbachs at gmail.co
m> To
Sent by: "Access Developers discussion and
accessd-bounces at d problem solving"
atabaseadvisors.c <accessd at databaseadvisors.com>
om cc
Subject
02/14/2005 11:57 Re: [AccessD] A2000: DELETE query
AM with JOIN
Please respond to
"Access
Developers
discussion and
problem solving"
<accessd at database
advisors.com>
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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
------------------------------------------------------------------------------
Electronic Privacy Notice. This e-mail, and any attachments, contains information that is, or may be, covered by electronic communications privacy laws, and is also confidential and proprietary in nature. If you are not the intended recipient, please be advised that you are legally prohibited from retaining, using, copying, distributing, or otherwise disclosing this information in any manner. Instead, please reply to the sender that you have received this communication in error, and then immediately delete it. Thank you in advance for your cooperation.
==============================================================================