Delete Query Issue

  • Thread starter Thread starter James Monroe
  • Start date Start date
J

James Monroe

Greetings:

Getting frustrated I can't figure this out....

Two tables are in question. All I want to do is to delete records in
Table1 IF that record also appears in Table2.

Simple as that, but it's driving me nuts. (Perhaps further nuts would
be more accurate.)

Thanks for any help,

James
 
Greetings:

Getting frustrated I can't figure this out....

Two tables are in question. All I want to do is to delete records in
Table1 IF that record also appears in Table2.
Click to expand...

You'll need to post a bit more information. How are the tables
related? What have you tried (please post the SQL)? What error, if
any, did you get?

Short but HIGHLY SPECULATIVE answer, since I don't know how your
tables are structured: create a Query joining from the Primary Key
field in Table1 to the corresponding Foreign Key field in Table2.
Change the query to a Delete query and select the * pseudo-field from
Table1 as the "delete from" field. The SQL would resemble

DELETE Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID;
 
This should work:

DELETE * FROM TABLE1
WHERE TABLE1.FKID = TABLE2.PKID

I would also check the database relationship between the
tables before you delete.
 
This should work:

DELETE * FROM TABLE1
WHERE TABLE1.FKID = TABLE2.PKID

I would also check the database relationship between the
tables before you delete.
Click to expand...

ummm... Trent, that actually won't work since you're not including
Table2 in the FROM clause. You can't just pull a table in from left
field!
 
This should work:

DELETE * FROM TABLE1
WHERE TABLE1.FKID = TABLE2.PKID

I would also check the database relationship between the
tables before you delete.
Click to expand...


Yep, that's got it!

Thanks very much!
 
You'll need to post a bit more information. How are the tables
related? What have you tried (please post the SQL)? What error, if
any, did you get?

Short but HIGHLY SPECULATIVE answer, since I don't know how your
tables are structured: create a Query joining from the Primary Key
field in Table1 to the corresponding Foreign Key field in Table2.
Change the query to a Delete query and select the * pseudo-field from
Table1 as the "delete from" field. The SQL would resemble

DELETE Table1.*
FROM Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID;
Click to expand...

Got it!

Thanks for the reply, John.
 
ummm... Trent, that actually won't work since you're not including
Table2 in the FROM clause. You can't just pull a table in from left
field!
Click to expand...

Here's the SQL that works:

DELETE Table1.*, Table2.JoinedField
FROM Table1 INNER JOIN Table2 ON Table1.JoinedField =
Table2.JoinedField
WHERE (((Table1.JoinedField)=[Table2.JoinedField]));

I realize it's not the exact SQL that you or Trent suggested due to my
poor description of the issue, but it was close enough to get me on
the right track.

I really appreciate the help from both of you!

James
 
Back
Top