Update a column, selected by query
It's not common but it can work.  Under certain circumstances (and I'm not sure of the rules) you can update a column from a CTE or subquery.  My guess is that it's very similar to being able to update a view and probably has the same rules.  

The times I've done it it's quite a bit faster than joining back to the original table and updating it.