I had it somewhat different, maybe too complex:

;with which_col_a_cte (col_a, col_d, col_e) on (

select s.col_a, d.col_d, d.col_e

from destination d inner join

source s on d.e = s.b

)

update destination

set col.d = cte.col.a

from destination d inner join

which_col_a_cte cte on d.col_e = cte.col_e

and d.col_d = cte.col_d

Without any real data I wasn't able to test it, but I think it looks good.