Delete query based on two tables 1

[joebloeonthego]

I'm getting the 'operation must use an updatable query' error.

My problem is that I'm trying to delete all records in table A that have matching fields in table B (if it was just one field, I would do a IN(Select blah)).
Here's what I'm trying to do:

DELETE A.*
FROM A
INNER JOIN B 
ON A.reqdate = B.duedate AND A.plant = B.shipcode AND A.qty = B.ordqty AND A.partID = B.PN;

any suggestions?

 

[grande]

I've never had that problem. The only thing I can suggest is try putting your crieteria into brackets... That's caused trouble with me before.

Hope this helps...

-------------------------
Just call me Captain Awesome.

 

[joebloeonthego]

well here's the actual code in access:

DELETE [FL 830 RMD].*
FROM [FL 830 RMD] INNER JOIN [FL 862 RMD] ON ([FL 830 RMD].Request_Date = [FL 862 RMD].Due_Date) AND ([FL 830 RMD].Qty = [FL 862 RMD].ORDER_QTY) AND ([FL 830 RMD].Part_ID = [FL 862 RMD].Part_ID) AND ([FL 830 RMD].Plant = [FL 862 RMD].Ship_To_Code);

I get the same problem with the brackets.

 

[PHV]

What are the PrimaryKeys of [FL 830 RMD] and [FL 862 RMD] ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[traingamer]

Try a subquery like (qSubQuery) (tested):

 Select A.ID
FROM A
INNER JOIN B
ON A.reqdate = B.duedate AND A.plant = B.shipcode AND A.qty = B.ordqty AND A.partID = B.PN;

Then

DELETE A.*
FROM A 
WHERE (((A.ID) In (Select ID from qSubQuery)))

(Assuming that A has a unique id)

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[joebloeonthego]

thanx, it was a primary key issue.