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
0 Comments:
Post a Comment
<< Home