SQL Update with JOIN

I was trying to remember how to do an SQL UPDATE statement using fields from a JOIN. Its one of those things I don't do very often, and the syntax is not exactly intuitive. Its a good example of the power of SQL.

The general template for such a statement is:

UPDATE [target table]
SET clause
FROM [target table]
JOIN clause
WHERE clause

In my case I had two tables. The listing table contained items for sale and the pertinent fields were Price and Commission, both money fields. The CommissionRate table has the same fields. The goal was to update the Listing.Commission field from the CommissionRate table, by joining on the Price fields.

The UPDATE statement ended up as:

UPDATE Listing
SET Listing.Commission = CommissionRate.Commission
FROM Listing
INNER JOIN CommissionRate ON Listing.Price = CommissionRate.SaleAmount
WHERE Listing.Commission <> CommissionRate.Commission

Refactoring:

The Commission.SaleAmount field needs to be renamed to Commission.Price to be consistent within the model

The WHERE clause should include a condition to NOT apply the update to certain records that should not be bulk updated.