Lesson 9	Updating 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 in one table using values or conditions from another table. Similar to the INSERT statement, you will use the FROM clause in your statement.

Widget Sales Service

In the previous lesson, we helped Widget Sales Service (WSS) track employee raises by inserting data from the Employee table into the SalaryHistory table. After monitoring the SalaryHistory table for several months, WSS management has identified that employees are generally underpaid, risking staff turnover. To address this, WSS plans to issue a 10% raise to all employees who have not received a raise in the past 12 months. Below are the SalaryHistory and Employee tables.

SalaryHistory

LastName	FirstName	PriorSalary	RaiseDate
Duck	Donald	5000	2024-01-01
Jones	John	30000	2023-06-15
Fredericks	Fred	45000	2024-06-09

Employee

LastName	FirstName	CurrentSalary
Duck	Donald	10000
Mouse	Mickey	20000
Mouse	Minnie	30000
Jones	John	40000
Fredericks	Fred	50000

To accomplish this, we must update the CurrentSalary column in the Employee table to 1.1 times its current value for all employees who:

are not in the SalaryHistory table, or
are in the SalaryHistory table but have a RaiseDate more than 365 days before the current date.

Using a Join

Since the Employee table contains more employees than the SalaryHistory table, an inner join would exclude employees without salary history records. Instead, we use a LEFT JOIN to include all employees. The following Transact-SQL statement is explained in detail below.

Update in Transact-SQL


UPDATE Employee
SET CurrentSalary = CurrentSalary * 1.1
FROM Employee E LEFT JOIN SalaryHistory S
ON E.LastName = S.LastName
AND E.FirstName = S.FirstName
WHERE DATEDIFF(day, S.RaiseDate, GETDATE()) > 365
OR DATEDIFF(day, S.RaiseDate, GETDATE()) IS NULL;

Note: This syntax is specific to SQL Server (Transact-SQL). Other databases like MySQL or Oracle may use different syntax for updating with data from another table, such as a correlated subquery or the MERGE statement. Always consult the documentation for your specific database system.

Explanation of Each Part:

UPDATE Employee: Specifies the Employee table as the target for updates.
SET CurrentSalary = CurrentSalary * 1.1: Increases the CurrentSalary column by 10% for qualifying rows.
FROM Employee E LEFT JOIN SalaryHistory S: Uses a LEFT JOIN to include all employees, even those without salary history records. Aliases E and S represent the Employee and SalaryHistory tables, respectively.
ON E.LastName = S.LastName: Matches rows between tables based on the LastName column.
AND E.FirstName = S.FirstName: Further refines the match using the FirstName column for precision.
WHERE DATEDIFF(day, S.RaiseDate, GETDATE()) > 365: Filters employees whose last raise was more than 365 days ago.
OR DATEDIFF(day, S.RaiseDate, GETDATE()) IS NULL: Includes employees with no salary history (where RaiseDate is NULL).
LEFT JOIN rationale: Ensures all employees are included, even those without salary history records, which is critical for the update logic.
Aliasing explained: Aliases E and S improve readability and allow clear column references from both tables.
DATEDIFF explained: The DATEDIFF function calculates the number of days between RaiseDate and the current date, identifying outdated salary records.
GETDATE(): Returns the current system date, used with DATEDIFF to evaluate the time since the last raise.
NULL logic in outer joins: The LEFT JOIN results in NULL for RaiseDate for employees without salary history, which are included via the IS NULL condition.

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