Delete from table with left join to another

[mrsbean]

I want to delete records from tblInventory which have been orphaned -- There is not a matching record in tblPick, and the PickID is not 0. PickID is 0 when the production crew is adding inventory or subtracting inventory. The query is asking me to select which table I want to delete from. I want to delete from tblInventory, but I don't know how to get the syntax right. Here is the code:

DELETE tblInventory.PickID, tblPick.PickID
FROM tblInventory LEFT JOIN tblPick ON tblInventory.PickID = tblPick.PickID
WHERE (((tblInventory.PickID)<>0) AND ((tblPick.PickID) Is Null));

MrsBean

 

[Golom]

DELETE *
FROM tblInventory
WHERE tblInventory.PickID IN

(

SELECT tblInventory.PickID
FROM tblInventory LEFT JOIN tblPick 
     ON tblInventory.PickID = tblPick.PickID
WHERE tblInventory.PickID <> 0
  AND tblPick.PickID Is Null

)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

Something like this should work:

Delete From TblInventory WHERE PickID <> 0 and PickID not in (SElect PickID FROM tblPick)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design