Delete Query

[gittymoe]

Guys, I am trying to run a delete query that would identify records from an unmatched query and delete all these records from table CopyOfTblSVEPURCHASEORDERS. Below is what I have been trying without success. When I run this I receive the message of "The Specified Field '[PO No]' could refer to more than one table listed in the FROM clause of your SQL statement"

DELETE *
FROM CopyOfTblSVEPURCHASEORDERS
WHERE exists
(select 1
from CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr
where [PO No] = CopyOfTblSVEPURCHASEORDERS.[PO No]);


Thanks for any help/advise.

 

[Xeroh]

I would try fully qualifying [PO No] with CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr.[PO No]

 

[gittymoe]

Thanks, just to confirm, by qualifying you mean make these changes --where CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr.[PO No]--? See revised code below. If this is correct, I am receiving a slightly different error message "The Specified Field CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr.[PO No] could refer to more than one table listed in the FROM clause of your SQL statement"

DELETE *
FROM CopyOfTblSVEPURCHASEORDERS
WHERE exists
( select 1
from CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr
where CopyOfTblSVEPURCHASEORDERSWithoutMatchingSVEPurchaseOr.[PO No] = CopyOfTblSVEPURCHASEORDERS.[PO No]);

 

[Xeroh]

Yes, that's exactly what I meant. You also don't need the * in the DELETE statement, but I don't think that would cause your problem.

The format is:

DELETE FROM TableA
WHERE EXISTS (SELECT *
FROM TableB
WHERE TableB.ID1 = TableA.ID1)

...and that's what you have, so I don't know why it's not working. I will continue to investigate.