SQL error stating invalid column name when I have verification if it exists. Why?

Question

There is staging script, which creates new column DOCUMENT_DEFINITION_ID stages it with values of MESSAGE_TYPE_ID + 5 and then removes column MESSAGE_TYPE_ID.

First time everything run ok, but when I run script second time I'm getting this error:

Invalid column name 'MESSAGE_TYPE_ID'.

It makes no sense since, I have verification if that column exists.

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGIN
  UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 --Error here.. but condition is not met

Why?

Answer 1

Delayed resolution of names only happens on tables that don't exist. You will need to drop and create the entire table.

Answer 2

Try this

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'MESSAGE_TYPE_ID' AND TABLE_NAME = 'DOCUMENT_QUEUE')
BEGIN
   EXEC( 'UPDATE DOCUMENT_QUEUE SET DOCUMENT_DEFINITION_ID = MESSAGE_TYPE_ID + 5 ')
END

It bypasses the delayed name resolution by wrapping the update in dynamic statement.