how to delete from one table, based on criteria from two tables? 1

[as0125]

Hello all,

I'm having some problems with the following DELETE query:

DELETE TblHistory.*
FROM TblHistory, TblHistoryTemp
WHERE TblHistory.Name = TblHistoryTemp.Name
AND TblHistory.Release = TblHistoryTemp.Release
AND TblHistory.Period = TblHistoryTemp.Period;

When running it, I receive this message: Could not delete from specified tables. I'm a bit new with this, but I'm guessing that SQL doesn't like the way I listed the two tables. All examples I've seen using the "DELETE" command are only for one table. How do I get the query to work based on those criteria?

All help would be greatly appreciated!
Thanks!

 

[JonFer]

See this: thread701-909285

John

http://www.interautomateconsulting.com

 

[as0125]

Hi John,

Based on the thread you recommended, I went back and doubld-checked my tables. Both tables are already indexed using "Autonumber - No duplicates". But I'm still unable to run the delete query.

 

[sfreeman]

DELETE DISTINCTROW
tblHistory.*

FROM
tblHistory INNER JOIN tblHistoryTemp
ON (tblHistory.Period = tblHistoryTemp.Period)
AND (tblHistory.Release = tblHistoryTemp.Release)
AND (tblHistory.Name = tblHistoryTemp.Name);

 

[JonFer]

The unique index (or primary key) needs to include the fields that are involved in the join. Just having a unique index is not enough.


John

http://www.interautomateconsulting.com

 

[as0125]

Thanks for all the help.

The "DELETE DISTINCTROW" did the trick!