| 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.