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

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.

SalaryHistory

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? Hold on to your hats…here we go:
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.