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

Deleting via a query with a join in it?

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
(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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
RE/
Did you try DISTINCTROW = query property "unique records"?


Bingo!

Thanks.
--
PeteCresswell
Nov 12 '05 #4

P: n/a
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?

Nov 12 '05 #5

P: n/a
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
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.