I am trying to run this delete query against a local Access tables, but I get error message
can't delete from specified tables
Please provide me the proper SQL to copy and paste.
Thank you very much in advance, Nathaniel
DELETE RO.*, RO.STATUS, CO.ID, CO.PRINTED_DATE
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));
This query depends on SYSADM_CUSTOMER_ORDER having a unique index on ID.
DELETE ro.*
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
If you do not have an index on SYSADM_CUSTOMER_ORDER, you can use something on these lines:
DELETE tmpreleasedorders.*
FROM tmpreleasedorders
WHERE status IN ( "c", "x" )
OR orderid IN (
SELECT orderid
FROM tmpreleasedorders AS ro
LEFT JOIN sysadm_customer_order AS CO
ON RO.orderid = CO.id
WHERE CO.id IS NULL
OR CO.printed_date IS NULL)
