By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,384 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

SQL Delete query won't work from query window but does from code...

P: n/a
I want to delete records from the "one" table of a one to may relationship.
There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query
window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

I know I could use a subquery, but why doesn't it work from the query window
and how do I get ti to work from the query window?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:9D******************@bignews4.bellsouth.net.. .
I want to delete records from the "one" table of a one to may relationship. There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

I know I could use a subquery, but why doesn't it work from the query window and how do I get ti to work from the query window?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

If you are determined not to use a sub-query, then you could add DISTINCTROW
as shown below. However, this is not supported by other database products
and the sub-query would make your SQL more portable.

DELETE DISTINCTROW a1.*
FROM a1 LEFT JOIN a2 ON a1.Field1=a2.Field1
WHERE a2.Field1 Is Null

Preferred solution from previous post:

DELETE FROM a1 WHERE a1.Field1
NOT IN (SELECT a2.Field1 FROM a2)
Now, as to why your first statement works in code (which indeed it does) but
not from the query window - I don't know. Perhaps someone can offer a
detailed explanation as a point of technical curiosity, but I would prefer
to simply accept a form of SQL which is unambiguous, portable and works in
code or from the Db window.

Fletcher
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.