469,317 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,317 developers. It's quick & easy.

HELP! How to delete recods in one table that match those in another table?

I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.

Here's the SQL code for the select query that DOES work:

SELECT tblEmailLetters_TEMP.*
FROM AAA_Temp_EmailImport INNER JOIN tblEmailLetters_TEMP ON
(AAA_Temp_EmailImport.MsgGroup = tblEmailLetters_TEMP.MsgGroup) AND
(AAA_Temp_EmailImport.MsgDesc = tblEmailLetters_TEMP.MsgDesc);

Here's what happens when I convert the above to a delete query:

DELETE tblEmailLetters_TEMP.*
FROM AAA_Temp_EmailImport INNER JOIN tblEmailLetters_TEMP ON
(AAA_Temp_EmailImport.MsgGroup = tblEmailLetters_TEMP.MsgGroup) AND
(AAA_Temp_EmailImport.MsgDesc = tblEmailLetters_TEMP.MsgDesc);

The delete version fails with a message that says "Could not delete
from specified tables."

I've done this successfully with other data base programs. Why does
this not work?

Thanks folks.
Nov 12 '05 #1
3 7907
On Fri, 10 Oct 2003 00:17:44 -0500, William Case
<re**********@nospam.zap> wrote:
I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.


goto the relationships window and doubleclick on the relation between
those two tables. Now you see the two fields in relation. Down the
form you can choose three possibilities. I choose them all three.
Especially the last one is important for you I think: delete ...
Hopefully it works now.
--
bebelino

Nov 12 '05 #2
On Fri, 10 Oct 2003 07:34:40 GMT, bebelino <a.*@c.d> wrote:
On Fri, 10 Oct 2003 00:17:44 -0500, William Case
<re**********@nospam.zap> wrote:
I cannot believe I'm having this much trouble doing such a simple
thing!

I have two tables with identical structure. I can use an A2K select
query to show the records in table 1 that match those in table two
based on an inner join between two unindexed fields.


goto the relationships window and doubleclick on the relation between
those two tables. Now you see the two fields in relation. Down the
form you can choose three possibilities. I choose them all three.
Especially the last one is important for you I think: delete ...
Hopefully it works now.


Thanks for this response Bebelino! Are you saying that the query
fails BECAUSE the tables are not linked by a relationship? If so, I
can simply create a relationship on the fly even though BOTH of these
tables are temporary. Correct?
Nov 12 '05 #3
On Fri, 10 Oct 2003 11:59:39 -0500, LaurenW <re**********@nospam.zap>
wrote:
Thanks for this response Bebelino! Are you saying that the query
fails BECAUSE the tables are not linked by a relationship? If so, I
can simply create a relationship on the fly even though BOTH of these
tables are temporary. Correct?


I suggest you try it out, because I haven't don't this before. I would
access the tables via code and then delete the approriate records.
good luck,
--
bebelino
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Hartmut Jaeger | last post: by
1 post views Thread by oyster | last post: by
6 posts views Thread by =?iso-8859-1?q?C=E9dric_Lucantis?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.