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


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

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.