Just searched the net trying to figure out how to do an update using an inner join. I figured it was possible, and I was right. Here’s how to do it:
SET Table1.Title = Table2.Title
FROM Table1, Table2
where Table1.ID = Table2.ID
If you are using aliases – for instance if you are updating a table you are joining to itself, use this code:
SET Table1Alias.Title = Table2Alias.Title
FROM Table1 as Table1Alias, Table2 as Table2Alias
where Table1Alias.ID = Table2Alias.ID
Another scenario you may encounter is where you want to update a table, but use an inner join to further filter the rows you want to update.
Here’s an example of how to do this:
UPDATE Table1 Set Field1 = Value
FROM Table1 as t1
INNER JOIN Table2 as t2 on t1.Field2 = t2.Field2
WHERE Field3 = Value
That seems too simple, but it eluded me for a long time.