You join TableA with cte which also is TableA, on ID field. Get rid of the hints, get rid of the cte, get rid of the join. It's a plain simple update. You can write select and turn it into update. Why bringing complexity into simple things?