Invalid column name on sql server update after column create

The root cause of the error is the newly added column name is not reflected in the sys.syscolumns and sys.columns table until you restart SQL Server Management Studio.

In this case you can avoid the problem by adding the column as NOT NULL and setting the values for existing rows in one statement as per my answer here.

More generally the problem is a parse/compile issue. SQL Server tries to compile all statements in the batch before executing any of the statements.

When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column. The best way round this is to do the DDL in a different batch from the DML.

If a statement both references a non existing column in an existing table and a non existent table the error may or may not be thrown before compilation is deferred.

You can either submit it in separate batches (e.g. by using the batch separator GO in the client tools) or perform it in a child scope that is compiled separately by using EXEC or EXEC sp_executesql.

The first approach would require you to refactor your code as an IF ... cannot span batches.

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)

      EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');

      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END;