It's not that difficult, you can do it this way or with 5 UPDATEs, one for each condition.

UPDATE m SET

Category1 = CASE WHEN ( Score > 0.0 AND Score < 0.4 ) THEN 1 ELSE Category1 END,

Category2 = CASE WHEN ( Score > 0.4 AND Score < 0.55 ) THEN 1 ELSE Category2 END,

Category3 = CASE WHEN ( Score > 0.55 AND Score < 0.66 ) THEN 1 ELSE Category3 END,

Category4 = CASE WHEN ( Score > 0.66 AND Score < 0.85 ) THEN 1 ELSE Category4 END,

Category5 = CASE WHEN ( Score > 0.85) THEN 1 ELSE Category5 END

FROM #MemberScore m

You're missing equal signs (=) somewhere in your conditions but I'm uncertain of which ones, maybe the on the bottom limits.