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

Help With Delete Query (error)

100+
P: 365
Hello everyone,

i have another task to complete,

i have two tables

Expand|Select|Wrap|Line Numbers
  1. tblEvent
  2. -EventID - PK Auto
  3. -PermaID - FK Num - tblPermavent
  4. -Date - Date
  5. --other irrelevant fields
and......
Expand|Select|Wrap|Line Numbers
  1. tblEventTemp
  2. -TempID - PK Auto
  3. -PermaID - FK Num - tblPermavent
  4. -Date - Date
What i want to do is delete from tblEventTemp when a match between PermaID and Date is found, (if the record exists in tblEvent, delete from tblEventTemp)

so far i have tried this SQL
Expand|Select|Wrap|Line Numbers
  1. DELETE tblEventTemp.* FROM tblEvent INNER JOIN tblEventTemp ON (tblEventTemp.Date = tblEvent.Date) AND (tblEvent.PermaID = tblEventTemp.PermaID);
What i tried to do here is delete * from tblEventTemp and made two joins between the matching fields PermaID and Date.

The query appears to work (in datasheet) however upon execution i get an error "Could not delete from specified tables"

Any Ideas would be most welcome (to resolve my SQL), i figure this would be the easiest and most efficient way of achieving his goal, but if there are better alternatives (that work) they will be welcomes with open arms....

thanks, Dan
Jul 13 '09 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,287
This should work:
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM tblEventTemp 
  2. WHERE EXISTS 
  3. (SELECT EventID FROM tblEvent
  4.  WHERE tblEventTemp.PermaID = tblEvent.PermaID 
  5.  AND tblEventTemp.Date = tblEvent.Date)
You may want to consider renaming the "Date" field!
Jul 13 '09 #2

100+
P: 365
@ChipR
Unfortunately it says "query must have at least one destination field"?
Jul 13 '09 #3

Expert 100+
P: 1,287
Try DELETE * FROM?
Wierd one.
Jul 13 '09 #4

100+
P: 365
that works a treat!!

thanks
Jul 13 '09 #5

Post your reply

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