UPDATE a table using a JOIN to another table (or to itself)

Soooooooooo, this is going to be one of those boring SQL-Server posts.

Despite writing T-SQL day in day out since forever, I often forget the syntax for an UPDATE statement that JOINS with another table (possibly itself). So here it is:

UPDATE
    p
SET
    p.ManagerEmail = m.Email
FROM
    Person p
INNER JOIN
    Person m
ON
    p.ManagerID = m.ID

Points to remember are:

The first part, 'UPDATE X' is simply 'UPDATE' followed by the alias of the table (you don't need to say the table's name there)

And (contrary to what some internet randos will tell you) you don't need to add a where clause to stop the update from applying to all rows of the table. It will only apply to the rows which are matched by the join condition used. (For an inner join at least ;-) )

I was using TimeSnapper to play back my work when I saw myself struggling with this query. And I remembered seeing myself struggle with the exact same thing another time (also via playback). So I'm putting this here, so that the act of typing it out might help cement the entire thing in my mind.

Also, I find it amusing that if you to do this in MySQL you basically write all the same things in a completely different order. So I'll include a MySQL example here for reference, too.

UPDATE
    Person p
INNER JOIN
    Person m
ON
    p.ManagerID = m.ID
SET
    p.ManagerEmail = m.Email

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.