SQL Delete doesn't work in query window but does in code...

[Matthew Wells]

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?

Thanks.

Matthew Wells

MW****@NumberCruncher.com

[Fletcher Arnold]

"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:wB******************@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?

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

[Matthew Wells]

I don't want to delete a2.field1, I want to delete records in a1 that don't
have a corresponding a2 record. The a2Field1 is there for the WHERE cluese
(WHERE a2.fieldd1 Is Null). Also, the example I gave is much simpler than
what I'm really doing because my a1 table has a 3 field pk and my a2 table
has a 7 field pk. I don't think there's a way to write a subquery when
there's more than one field in the a1 pk.

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:8a********************************@4ax.com...

Uh, why do you want to delete the a2.Field1 ?

DELETE a1.*, a2.Field1

should be:

DELETE a1.*

On Mon, 26 Jan 2004 09:33:13 -0500, "Matthew Wells"
<MW****@NumberCruncher.com> wrote:

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 querywindow.

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 windowand how do I get ti to work?

Thanks.

Matthew Wells

MW****@NumberCruncher.com