Deleting via a query with a join in it?

[(Pete Cresswell)]

Seems to me like I've done this. When it wasn't possible to delete the records
in question via a fairly complicated query I resorted to creating a work table
containing IDs of recs TB deleted, then did the deletes by joining the target
table to the work table.

I'm trying to do that right now and an error's popping to the effect the delete
cannot be done.

Anybody know the rules?
--
PeteCresswell

[Karl Donaubauer]

(Pete Cresswell) wrote:

Seems to me like I've done this. When it wasn't possible to delete
the records in question via a fairly complicated query I resorted to
creating a work table containing IDs of recs TB deleted, then did the
deletes by joining the target table to the work table.

I'm trying to do that right now and an error's popping to the effect
the delete cannot be done.

Anybody know the rules?

Did you try DISTINCTROW = query property "unique records"?

If that doesn't help you should post the SQL statement.

--
HTH
Karl
*********
Access-FAQ (German): http://www.donkarl.com

[David W. Fenton]

x@y.z ((Pete Cresswell)) wrote in
<kc************ *************** *****@4ax.com>:

Seems to me like I've done this. When it wasn't possible to
delete the records in question via a fairly complicated query I
resorted to creating a work table containing IDs of recs TB
deleted, then did the deletes by joining the target table to the
work table.

I'm trying to do that right now and an error's popping to the
effect the delete cannot be done.

Anybody know the rules?

What's the error?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[(Pete Cresswell)]

RE/

Did you try DISTINCTROW = query property "unique records"?

Bingo!

Thanks.
--
PeteCresswell

[Pink Panther]

Pete,

without DDL or the SQL to see what's going on it hard to say. I too
have encountered the problem before.

It seems that if you are trying to delete a parent record (ie the 1
side of the relationship) from an attribute of a child (the N side);
you can't .

Works the other way though.

To get around this don't use a join, use

DELETE *
FROM Parent
WHERE PARENT_KEY
NOT IN(SELECT YourPK FROM child where attribute = value)

or somesuch derivative (NOT EXISTS may be quicker). Slower than a join
but does the trick and doesn't use temp tables.

Peter

"(Pete Cresswell)" <x@y.z> wrote in message news:<kc******* *************** **********@4ax. com>...

Seems to me like I've done this. When it wasn't possible to delete the records
in question via a fairly complicated query I resorted to creating a work table
containing IDs of recs TB deleted, then did the deletes by joining the target
table to the work table.

I'm trying to do that right now and an error's popping to the effect the delete
cannot be done.

Anybody know the rules?

[(Pete Cresswell)]

RE/

To get around this don't use a join, use

DELETE *
FROM Parent
WHERE PARENT_KEY
NOT IN(SELECT YourPK FROM child where attribute = value)

That one's going into my "keepers" file....

As much as I've grown to love (and depend on) the graphical query builder, I can
see that it's exacted a price in my lack of familiarity with raw SQL.
--
PeteCresswell