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.


LastName FirstName PriorSalary RaiseDate






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.

Line 1 indicates which table is going to be updated by the statement. In this case, Employee will be updated. Line 2 shows the column that will be updated in the Employee table and the new value that it will be set to. Line 3 is very important because it does many things. The ON and AND statements indicate which values will be used to join the tables together. The WHERE clause limits the number of records that are updated in the Employee table. Because we are using an outer join to return all data from the Employee table,
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