You're not referencing your CTE on your UPDATE statement. Additionally, you don't have anything to make a relationship (and that's caused by the previous reason).

You don't actually need a CTE, something as simple as a JOIN should work.

UPDATE ce SET

FormerBranch = CASE WHEN e.EntityName IS NULL

THEN ' '

ELSE e.EntityName

END

FROM ClientExtra ce

JOIN Clients c ON ce.ClientRef = c.ClientRef

LEFT

JOIN Entities e ON c.BranchRef = e.EntityRef AND e.Type = 3