SUBSELECT contains an error - but the sql statement continues

I have the following code example:

create table #tempmembers (
    memberid int
)

update Members set 
Member_EMail = NULL
where Member_ID in (select member_id from #tempmembers)

The subquery contains an error because it #tempmembesdoes not contain a column with a name member_id, but sql statements run WITHOUT any errors and do not update the rows.

If I add only one line to #tempmembers:

create table #tempmembers (
    memberid int
)
insert into #tempmembers select 1

update Members set 
Member_EMail = NULL
where Member_ID in (select member_id from #tempmembers)

it still runs without any errors, but this time ALL member entries will be affected.

Why doesn't the SQL statement work completely? And if the failed sub-selection is evaluated as NULL - if the update of all rows in Membersoccurs not only if it was:

update Members set 
Member_EMail = NULL
where Member_ID not in (select member_id from #tempmembers)