Help With Delete Query (error)

[Dan2kx]

Hello everyone,

i have another task to complete,

i have two tables

tblEvent
-EventID - PK Auto
-PermaID - FK Num - tblPermavent
-Date - Date
--other irrelevant fields

and......

tblEventTemp
-TempID - PK Auto
-PermaID - FK Num - tblPermavent
-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

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

[ChipR]

This should work:

DELETE FROM tblEventTemp 
WHERE EXISTS 
(SELECT EventID FROM tblEvent
 WHERE tblEventTemp.PermaID = tblEvent.PermaID 
 AND tblEventTemp.Date = tblEvent.Date)

You may want to consider renaming the "Date" field!

[Dan2kx]

This should work:

DELETE FROM tblEventTemp 
WHERE EXISTS 
(SELECT EventID FROM tblEvent
 WHERE tblEventTemp.PermaID = tblEvent.PermaID 
 AND tblEventTemp.Date = tblEvent.Date)

You may want to consider renaming the "Date" field!

Unfortunately it says "query must have at least one destination field"?

[ChipR]

Try DELETE * FROM?
Wierd one.

[Dan2kx]

that works a treat!!

thanks