update [R33HypInfa].[dbo].[DF012_HFM_Staging]

set ICP=

(select B.ICP

from [R33HypInfa].[dbo].[DF012_Look_up_hfm] A

,[R33HypInfa].[dbo].[DF012_HFM_Staging]B

Where A.Account=B.Account AND A.Icp='VS001');

the way this is written explains why you have duplicate values, as I'm willing to bet money that the select returns more than one B.ICP value if run independantly of the update.

what is it you are trying to achieve?