Why does my update statement update every row?

Question

Problem:

My final update statement should error out due to syntax and rollback the transaction, the expected error should be similar to:

Msg 207, Level 16, State 1, Line 45 Invalid column name 'FakeTest1'

I'm curious as to why SQL Server updates every row in the column when the subquery referencing a non-existent column is allowed to execute.

Background

I am creating a Temp table with two columns, FakeID and FakeVarchar.

 SELECT  [FakeID], [FakeVarchar]
 INTO [#T]
 FROM [CTE];

When my UPDATE statement uses this WHERE clause specifying the column [ID] in the SELECT SUBQUERY, it updates the entire table instead of erroring on parsing the command.

WHERE [FakeTableDestination].[ID] IN
(
    SELECT [ID]
    FROM [#T]
);

The column that should be in the subquery for it to run correctly would be FakeID. This statement obviously works and it updates as expected.

WHERE [FakeTableDestination].[ID] IN
(
    SELECT [FakeID]
    FROM [#T]
);

When I replace FakeID / ID with any other column name, it fails as expected.

WHERE [FakeTableDestination].[ID] IN
(
    SELECT [NotActuallyAColumnInThisTableEither]
    FROM [#T]
);

Full SQL code that updates the entire table when it should error out instead

IF OBJECT_ID('dbo.FakeTableSource', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableSource];
END;

CREATE TABLE [dbo].[FakeTableSource]
( 
             [FakeID] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
BEGIN
    DROP TABLE [#T];
END;

IF OBJECT_ID('dbo.FakeTableDestination', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableDestination];
END;

CREATE TABLE [dbo].[FakeTableDestination]
( 
             [ID] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

INSERT INTO [dbo].[FakeTableSource]( [FakeID], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ), ( 4, 'Four' );

INSERT INTO [dbo].[FakeTableDestination]( [ID], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Five' ), ( 5, 'Four' );

WITH CTE
     AS (SELECT [FakeID], [FakeVarchar]
         FROM [dbo].[FakeTableSource])
     SELECT  [FakeID], [FakeVarchar]
     INTO [#T]
     FROM [CTE];

UPDATE [dbo].[FakeTableDestination]
  SET [FakeVarchar] = [src].[FakeVarchar]
FROM [dbo].[FakeTableSource] AS [SRC]
WHERE [FakeTableDestination].[ID] IN
(
    SELECT [ID]
    FROM [#T]
);

SELECT *
FROM [dbo].[FakeTableSource];

SELECT *
FROM [dbo].[FakeTableDestination];

SELECT *
FROM [#t];

Full SQL Code that is syntactically correct and updates the values as expected

IF OBJECT_ID('dbo.FakeTableSource', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableSource];
END;

CREATE TABLE [dbo].[FakeTableSource]
( 
             [FakeID] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
BEGIN
    DROP TABLE [#T];
END;

IF OBJECT_ID('dbo.FakeTableDestination', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableDestination];
END;

CREATE TABLE [dbo].[FakeTableDestination]
( 
             [ID] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

INSERT INTO [dbo].[FakeTableSource]( [FakeID], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ), ( 4, 'Four' );

INSERT INTO [dbo].[FakeTableDestination]( [ID], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Five' ), ( 5, 'Four' );

WITH CTE
     AS (SELECT [FakeID], [FakeVarchar]
         FROM [dbo].[FakeTableSource])
     SELECT  [FakeID], [FakeVarchar]
     INTO [#T]
     FROM [CTE];

UPDATE [dbo].[FakeTableDestination]
  SET [FakeVarchar] = [src].[FakeVarchar]
FROM [dbo].[FakeTableSource] AS [SRC]
WHERE [FakeTableDestination].[ID] IN
(
    SELECT [FakeID]
    FROM [#T]
);

SELECT *
FROM [dbo].[FakeTableSource];

SELECT *
FROM [dbo].[FakeTableDestination];

SELECT *
FROM [#t];

What I discovered

The WHERE clause seems to be what allows this UPDATE to happen. For example, I'll change the column name in FakeTableDestination from FakeID to Test. I can then reference the Test column from #Teven though the column is still aliased as FakeID. This causes the entire column to update during the update statement.

SQL Code Illustrating That Behavior

IF OBJECT_ID('dbo.FakeTableSource', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableSource];
END;

CREATE TABLE [dbo].[FakeTableSource]
( 
             [FakeID] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

IF OBJECT_ID('tempdb.dbo.#T', 'U') IS NOT NULL
BEGIN
    DROP TABLE [#T];
END;

IF OBJECT_ID('dbo.FakeTableDestination', 'U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[FakeTableDestination];
END;

CREATE TABLE [dbo].[FakeTableDestination]
( 
             [Test] INT PRIMARY KEY, [FakeVarchar] VARCHAR(10)
);

GO

INSERT INTO [dbo].[FakeTableSource]( [FakeID], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Three' ), ( 4, 'Four' );

INSERT INTO [dbo].[FakeTableDestination]( [Test], [FakeVarchar] )
VALUES( 1, 'One' ), ( 2, 'Two' ), ( 3, 'Five' ), ( 5, 'Four' );

WITH CTE
     AS (SELECT [FakeID], [FakeVarchar]
         FROM [dbo].[FakeTableSource])
     SELECT  [FakeID], [FakeVarchar]
     INTO [#T]
     FROM [CTE];


UPDATE [dbo].[FakeTableDestination]
  SET [FakeVarchar] = [src].[FakeVarchar]
FROM [dbo].[FakeTableSource] AS [SRC]
WHERE [FakeTableDestination].[Test] IN
(
    SELECT [Test]
    FROM [#T]
);

SELECT *
FROM [dbo].[FakeTableSource];

SELECT *
FROM [dbo].[FakeTableDestination];

SELECT *
FROM [#t];

Things I tried/thought about

What if ID is a name of a column in the temp table and it's hidden?

SELECT 
       *
FROM [tempdb].[sys].[syscolumns]
WHERE [name] = 'FakeID';

Ok, I see ID listed here and I see other columns, like xtype, typestat, etc. So what if I try one of those other columns instead? I tried substituting xtype / typestat and they failed on syntax too.

Maybe it's because you're using a temp table?

I get this same behavior with table variables and physical tables.

Table Variable Code

IF OBJECT_ID('dbo.FakeTableSource', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[FakeTableSource];
END;

CREATE TABLE [dbo].[FakeTableSource]
([FakeID]      INT
 PRIMARY KEY, 
 [FakeVarchar] VARCHAR(10)
);

GO

IF OBJECT_ID('dbo.FakeTableDestination', 'U') IS NOT NULL
BEGIN
DROP TABLE [dbo].[FakeTableDestination];
END;

CREATE TABLE [dbo].[FakeTableDestination]
([ID]          INT
 PRIMARY KEY, 
 [FakeVarchar] VARCHAR(10)
);

GO

DECLARE @T TABLE
([FakeID]      INT
 PRIMARY KEY, 
 [FakeVarchar] VARCHAR(10)
);

INSERT INTO [dbo].[FakeTableSource]
(
       [FakeID], 
       [FakeVarchar]
)
VALUES
       (1, 
        'One'),
       (2, 
        'Two'),
       (3, 
        'Three'),
       (4, 
        'Four');

INSERT INTO [dbo].[FakeTableDestination]
(
       [ID], 
       [FakeVarchar]
)
VALUES
       (1, 
        'One'),
       (2, 
        'Two'),
       (3, 
        'Five'),
       (5, 
        'Four');

WITH CTE
     AS (SELECT 
                [FakeID], 
                [FakeVarchar]
         FROM [dbo].[FakeTableSource])

     INSERT INTO @T
     (
            [FakeID], 
            [FakeVarchar]
     )
     SELECT 
            [FakeID], 
            [FakeVarchar]
     FROM [CTE];

UPDATE [dbo].[FakeTableDestination]
  SET 
      [FakeVarchar] = [src].[FakeVarchar]
FROM [dbo].[FakeTableSource] AS [SRC]
WHERE 
      [FakeTableDestination].[ID] IN
(
    SELECT 
           [ID]
    FROM @T
);

Physical Table Code Snippet

SELECT 
     [FakeID], 
     [FakeVarchar]
INTO dbo.T
FROM [CTE];

UPDATE [dbo].[FakeTableDestination]
  SET 
      [FakeVarchar] = [src].[FakeVarchar]
FROM [dbo].[FakeTableSource] AS [SRC]
WHERE 
      [FakeTableDestination].[ID] IN
(
    SELECT 
           [ID]
    FROM T
);

What if you change the UPDATE to NOT IN?

It does not update any of the rows in that column.

What versions of SQL Server did you try this on? SQL Server 2012 SP1, SP2, 2016 SP 2.

What did the query plans look like?

Query I believe should error on syntax

Query that updates correctly