>>What is the type of the Subject_E column in SQL Server and what is the collation of this column?
>
>Subject_ should have been Body_E and is defined Text (still not converted yet). That is why I have to cast it.

This works as well for me:

create table #test(Subject_E ntext)

insert into #test (Subject_E) values ('Testing [OldMemberTableName]'), ('Testing [OLDMEMBERTABLENAME]')

UPDATE #test SET Subject_E=replace(cast(Subject_E as nvarchar(max)) ,'[OldMemberTableName]','[Member]')
select * from #test

Both values are replaced.