CRUD Statements  «Prev  Next»
Lesson 10 Deleting data from values in another table
Objective Construct DELETE Transact-SQL statements that retrieve data from another table.

Deleting Data from Values in another Table

Certainly, when working with SQL Server 2019, ensuring referential integrity between parent and child tables is crucial. One common scenario involves deleting records from a child table based on specific conditions related to the parent table. To accomplish this, it is necessary to retrieve data from the parent table before executing the DELETE Transact-SQL statement on the child table.

Scenario Description:

Assume we have two tables:
  1. `Customers`: This is the parent table, containing customer information.
    • Columns: CustomerID, CustomerName, AccountStatus
  2. `Orders`: This is the child table, containing orders placed by customers.
    • Columns: OrderID, CustomerID, OrderDate, OrderAmount
The requirement is to delete all orders from inactive customers. Inactive customers are identified by the 'AccountStatus' column in the 'Customers' table, where 'Inactive' indicates an inactive status.

SQL Statement to Achieve the Requirement:

-- Step 1: Retrieve and Review Data from Parent Table
SELECT c.CustomerID, o.OrderID, o.OrderDate, o.OrderAmount
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.AccountStatus = 'Inactive';

-- Step 2: Execute DELETE Statement on Child Table
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Customers
    WHERE AccountStatus = 'Inactive'
  • Step 1: Before executing the DELETE statement, it is crucial to review the data to ensure that the correct records are being targeted. A SELECT statement is used to retrieve and display orders associated with inactive customers. This step helps in verifying that the subsequent DELETE operation will affect the intended records.
  • Step 2: The DELETE statement is executed on the 'Orders' table. The WHERE clause specifies that only orders belonging to inactive customers should be deleted. This is determined by a subquery, which retrieves the CustomerID values from the 'Customers' table where the 'AccountStatus' is 'Inactive'.

Important Considerations:

  • Data Verification: Always verify the data before executing a DELETE statement, especially when dealing with related tables. This helps in preventing accidental deletion of unintended records.
  • Transaction Control: Consider wrapping the DELETE statement within a transaction, providing the ability to rollback changes if something goes wrong.
  • Backup: Ensure that there are recent backups of the data, or consider taking a backup before executing the DELETE statement, to provide a restore point in case of any issues.
  • Referential Integrity: Make sure that referential integrity constraints, such as foreign keys, are appropriately configured to maintain data consistency.

By following these practices, you can ensure that the DELETE operation is conducted accurately, maintaining the integrity of the data within your SQL Server 2019 environment.

A WSS employee resigns

Fred Fredericks has decided to resign from WSS. Business rules dictate that former employees stay in the Employee table, but get deleted from the SalaryHistory table.


EmployeeID PriorSalary RaiseDate
101 5000 01/01/99
104 30000 06/15/97
105 45000 06/09/99

Employee Table

EmployeeID LastName FirstName CurrentSalary
101 Duck Donald 10000
102 Mouse Micky 20000
103 Mouse Minnie 30000
104 Jones John 40000
105 Fredericks Fred 50000

To accomplish this, we must delete from the SalaryHistory table by getting the values from the Employee table for Fred Fredericks. Are you ready to see how to do this?
An alternative way to represent the above statement is to provide the join information in the WHERE clause, like this:

Deleting Data - Exercise

Click the Exercise link below on the left to practice deleting data with values from another table.
Deleting Data - Exercise
In the next lesson, you will learn how to use accommodate different datatypes in your queries.