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

Delete query syntax trouble

TheSmileyCoder
Expert Mod 100+
P: 2,321
I have a table tbl_Dialog:
KEY_Dialog, ID_Obs .....

Table tbl_Obs
KEY_Obs, ID_Review .....

They are linked by KEY_Obs and ID_Obs, and I want to delete all records in tbl_Dialog having ID_Review=1200.

I tried:
Expand|Select|Wrap|Line Numbers
  1. myDB.Execute "DELETE tbl_Dialog.* FROM tbl_Dialog INNER JOIN tbl_Obs ON (tbl_Dialog.ID_Obs=tbl_Obs.KEY_Obs) " & _
  2.          '            " WHERE (((tbl_Obs.ID_Review) In " & listRelatedReviews(lngRevID) & ") AND (tbl_Dialog.dt_Date=" & formatSQLdate(dtSubmittal) & "));"
ListRelatedReviews will return a string containing other relevant Review IDs, in this case the string returned would be "(1200,472)"

My problem is that instead of only deleting the records in tbl_Dialog, the code also deleted the records in tbl_Obs.

What should I change to only delete the records from tbl_Dialog?
Jun 22 '12 #1

✓ answered by Rabbit

Take out the join and filter using a subquery in the where clause.

Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,315
Take out the join and filter using a subquery in the where clause.
Jun 22 '12 #2

dsatino
100+
P: 393
You may have a relationship between the fields with a cascading deletion.
Jun 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
I suggest you post the actual SQL code run, rather than the code which creates it - which doesn't appear to match the comments in the post as it stands.

As it stands, the syntax appears to be correct, but there's no filtering in this version so ...
Jun 25 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.