AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com  


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

Delete records in a table based on criteria in a different table using a subquery

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


Delete records in a table based on values in a different table using a subquery

Delete records from the orders table using criteria from the customers table

Discussion:

This time, we have a request from management to delete all orders which came in from Los Angeles for a customer with a last name Orlando.  They do not care about table relationships and the fact that we do not have a state field and a last name field in the orders table.  This is not a problem for us since we can use a subquery to easily delete records in one table based on criteria in a different table.  In a production environment, follow the steps below: 

Step 1: Run a SELECT statement to uniquely identify and verify records for deletion.  

SELECT *

FROM tbls_Orders_DEL

WHERE CustomerID IN

(SELECT CustomerID FROM Customers

WHERE city= "Los Angeles" AND lastname = "Orlando") 

Step 2: Turn on cascade deletes for the relationship between the tables Orders and ProductsOrders.   

Step 3: Run the DELETE statement

DELETE

FROM tbls_Orders_DEL

WHERE CustomerID IN

(SELECT CustomerID FROM Customers

WHERE city= "Los Angeles" AND lastname = "Orlando")

 

Result:

 

Step 4: Turn off cascade deletes for the relationship between the tables Orders and ProductsOrders.