Delete records from a table

[Hemish]

Hi

I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying

"Specify The Table Containing The Records you want to Delete"

Below is the query in SQL

DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);

I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.

Thanks in Advanced

 

[Jon K]

You can't use a JOIN in a Delete Query. Instead you can use a subselect.

DELETE tblStockRequired.*
FROM tblStockRequired
WHERE OrderNumber & " " & StockCode & " " & StockQTY IN (SELECT OrderNumber & " " & StockCode & " " & QTY FROM tblOrderCompleted);
.

 

[Hemish]

Thanks allot, i tried that and it was fine.