CODEWITHSUNDEEP
ms-access
Nathaniel_613
Nathaniel_613

Reputation: 179

delete records in one table based on criteria in a different table

I want to delete records in one table based on criteria in a different table , but I am getting the error message :"specify the table containing records you want to delete" (I only want to delete all of the records in table "RO", table "CO" is only for criteria purposes). Please provide me with the proper SQL to copy and paste (I dont know SQL. I use the query builder). 

DELETE CO.ID, CO.PRINTED_DATE, RO.STATUS, RO.orderID, RO.CREATE_DATE, RO.NAME, RO.cust_ID, RO.lines, RO.PICKER, RO.COMMENTS, RO.sls_rep, RO.weight, RO.PO_number, RO.Tracking_Number, RO.ship_via, RO.nmbrOfBoxes
FROM tmpReleasedOrders AS RO 
LEFT JOIN SYSADM_CUSTOMER_ORDER AS CO ON RO.orderID = CO.ID
WHERE (((RO.STATUS)="C" Or (RO.STATUS)="X")) OR (((CO.ID) Is Null)) OR (((CO.PRINTED_DATE) Is Null));

Upvotes: 0

Views: 5140

Answers (1)

JohnFx
JohnFx

Reputation: 34909

Here is the syntax for a delete query that has conditions from another table:

DELETE tmpReleasedOrders.*
FROM tmpReleasedOrders AS RO 
     LEFT JOIN SYSADM_CUSTOMER_ORDER AS CO ON (RO.orderID = CO.ID)
WHERE (RO.STATUS="C") OR (RO.STATUS="X") OR (CO.ID Is Null)

Upvotes: 1

Related Questions