Delete on joined table

Once I wrote how DISTINCTROW predicate helps to specify what table to UPDATE or DELETE. Below is one more case when DISTINCTROW could help.

Let’s say you want to delete records from the table, taking into consideration result of aggregate query, for example - delete customers, which have no orders. In Northwind.mdb we make a query qryCusromersWithOrders:

SELECT Orders.CustomerID
FROM Orders
GROUP BY Orders.CustomerID

Delete query SQL you can write as:

DELETE Customers.*
FROM Customers LEFT JOIN qryCusromersWithOrders ON Customers.CustomerID = qryCusromersWithOrders.CustomerID
WHERE qryCusromersWithOrders.CustomerID Is Null

But this query will fail with error “Could not delete from specified tables”, as you have non-updateable query in join. The trick is to add DISTINCTROW as:

DELETE DISTINCTROW Customers.*
FROM Customers LEFT JOIN qryCusromersWithOrders ON Customers.CustomerID = qryCusromersWithOrders.CustomerID
WHERE qryCusromersWithOrders.CustomerID Is Null