Invalid Column Name Error after ALTER and UPDATE

I am trying to UPDATE a specific column within a table, but I get an error due to the fact that when SQL compiles, the column name IssueTimeUTC does not actually exist. Here is my sample code:

WHILE  @startissuetimeCAST(@tempdate AS SMALLDATETIME)

            UPDATE Intraday_Forecast_temp
            SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
            WHERE

DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
END

I have read multiple similar posts, but I cannot make anything work therefore I am kindly asking you if you could help me.

The error I get is this:


Msg 207, Level 16, State 1, Line 56 Invalid column name 'IssueTimeUTC'.

Update: So basically I was not able to find an exact solution to this specific problem but I just found a way to go ''around'' the problem instead. So this is the updated code I used.

WHILE  @startissuetimeCAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)
    END

I know that this is not probably the most elegant solution but I initially defined the Intraday_Forecast_temp table WITH the column IssueTimeUTC , then I drop it and add it again. This way, SQL stop complaining that the column does not exist upon compilation :)

You cannot add the column and use it in the same batch.

So if you execute your code in SSMS just add GO between your commands like this:

ALTER TABLE Intraday_Forecast_temp
        ADD IssueTimeUTC SMALLDATETIME 

go ----------------------------CAST(@tempdate AS SMALLDATETIME)

        UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=Dateadd(d,-1,CAST(@tempdate AS SMALLDATETIME))
        WHERE DateTimeUTC<=CAST(@tempdate AS SMALLDATETIME)

If it's a part of stored procedure, wrap your update in EXEC:

exec ('UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)')

UPDATE

In your case you can use wrapping in exec this way:

declare @tempdate date = getdate(); -- or whatever it should be
declare @sql nvarchar(4000)= N'UPDATE Intraday_Forecast_temp
        SET IssueTimeUTC=CAST(@tempdate AS SMALLDATETIME)
        WHERE DateTimeUTC>CAST(@tempdate AS SMALLDATETIME)';

exec sp_executesql @sql, N'@tempdate date', @tempdate = @tempdate;

StackExchange Database Administrators Q#193106, answer score: 10