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.
Next → ← PreviousUPDATE Person p INNER JOIN Person m ON p.ManagerID = m.ID SET p.ManagerEmail = m.Email
I'm currently writing a book about how to build your first product. If you want to build your first product, please sign up to be notified when the book is available.