Kenneth Fisher shows that you can directly update a table referenced in a common table expression:
CTEs are cool things. You can essentially create one or more in-line view(s) within your query. One thing that isn’t overly well known is that you can actually update the data within the CTE. No, I don’t mean using using the UPDATE statement with a CTE but actually running the update through the CTE.
This is really powerful when combined with window functions, like only updating the first record given a particular partition. You can also delete, which makes duplicate detection and deletion fairly straightforward.