SQLConfiguration SQLConfiguration


CRUD Statements  «Prev  Next»
Lesson 9Updating data from values in another table
Objective Construct UPDATE Transact-SQL statements that retrieve data from another table.

Updating Data from Values in another Table in SQL Server

In this lesson you will learn how to update data, using values or conditions in another table. As with the INSERT statement, you will use the FROM clause in your statement.

Widget Sales Service

In the previous lesson, we helped WSS track who was getting raises, and when. We did this by inserting values from WSS’s Employee table into their SalaryHistory table.
After tracking the SalaryHistory table for a few months, management has determined that employees are generally underpaid, and WSS is at risk of losing employees. WSS now plans to issue a raise of 10% to everyone who has not had a raise in the past 12 months.
Below are WSS's SalaryHistory and Employee tables.

SalaryHistory

LastName FirstName PriorSalary RaiseDate
DuckDonald500001/01/99
JonesJohn3000006/15/97
FredericksFred4500006/09/99

Employee


LastName

FirstName

CurrentSalary

DuckDonald10000
MouseMickey20000
MouseMinnie30000
JonesJohn40000
FredericksFred50000

To accomplish this, we must update the CurrentSalary column of the Employee table so that it becomes 1.1 times the value in the table currently for all employees who:
  1. are not in the SalaryHistory table or
  2. are in the SalaryHistory table, but have a RaiseDate that is more than 365 days past the current date.

Using a join

Because there will be more employees in the Employee table than are in the SalaryHistory table, performing a regular (inner) join will not produce the desired results. We need to construct an outer join to retrieve all data in the Employee table. The following Transact-SQL statement shows how to construct this UPDATE Transact-SQL statement.


Update On Sql Statement

Updating Data Values - Exercise

Click the Exercise link below to practice updating data with values from another table.
In the next lesson, you will learn how to delete data using values from another table.
Updating Data Values - Exercise