Lesson 24. Updating Data With A Join

Sometimes you need to update data, but the columns necessary to identify what needs to be updated cannot be solely found in the target table. In that scenario, you can use a JOIN to get the columns or other information you need.

The syntax for an UPDATE with a JOIN is wildly different than a plain vanilla join.

UPDATE JOIN Syntax

UPDATE [target table alias]
SET [target column] = [new value]
FROM [target table]
JOIN [other table]
ON [target table key] = [other table key]
WHERE [conditions that you specify]

Examples

Changing A User’s Last Name

Scenario: Barb is getting married and is taking her husband’s last name, so her last name needs to be changed in the system.

The problem is, we only know Barb as Barb and lord knows how many Barbaras there are in the massive multinational we all work at. We can’t just update ALL the Barbs in the Person table and hope ONE of them is right!

We do have Barb’s globally unique ID called a national ID number though. It’s 969985265. But that value isn’t stored in the Person table. That’s in the employee table. UPDATE with JOIN to the rescue!

USE AdventureWorks2016


--Here is how we find Barb using her national ID number.
SELECT 
p.FirstName,
p.LastName,
e.NationalIDNumber,
e.Gender
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.NationalIDNumber = '969985265'

--now to update Barb's last name of Decker to her new last name Mudd.
UPDATE p
SET 
p.LastName = 'Mudd',
p.ModifiedDate = CURRENT_TIMESTAMP --don't get in a hurry and forget to populate audit columns
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.NationalIDNumber = '969985265'

--Let's confirm the change was made.
SELECT 
p.FirstName,
p.LastName,
e.NationalIDNumber,
e.Gender
FROM Person.Person p
JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID
WHERE e.NationalIDNumber = '969985265'