Database Upgrade - Invalid column name
<p>I am trying to migrate data during a database upgrade and I don't understand why scenario 1 is working but scenario 2 is throwing an exception:</p>

<p><strong>Scenario 1</strong></p>

<ul>
<li>Migrate data from Obsolete<strong>Table</strong> into New<strong>Table</strong>

<ul>
<li>Works if ObsoleteTable and NewTable both exists</li>
<li><strong>No error</strong> if ObsoleteTable has been removed already</li>
</ul></li>
</ul>

<p>Query Scenario 1:</p>

<pre><code>-- NewTable already created
IF EXISTS(select * from sys.tables where name = 'NewTable')
AND EXISTS(select * from sys.tables where name = 'ObsoleteTable')
BEGIN
    UPDATE newTable
    SET newTable.Name = obsoleteTable.Name
    FROM dbo.NewTable newTable
    INNER JOIN dbo.ObsoleteTable obsoleteTable
        ON obsoleteTable.Id = newTable.Id
END
-- ObsoleteTable will be removed after this step
</code></pre>

<p><strong>Scenario 2</strong></p>

<ul>
<li>Migrate from Obsolete<strong>Column</strong> to New<strong>Column</strong>

<ul>
<li>Works if ObsoleteColumn and NewColumn both exists</li>
<li><strong>Gives error!!!</strong> if ObsoleteColumn is removed already</li>
</ul></li>
</ul>

<p>Query Scenario 2:</p>

<pre><code>-- NewColumn has been created 
IF EXISTS(select * from sys.columns where object_id = object_id('MyTable') AND name = 'ObsoleteColumn')
AND EXISTS(select * from sys.columns where object_id = object_id('MyTable') AND name = 'NewColumn')
BEGIN
    UPDATE MyTable
    SET NewColumn = ObsoleteColumn
END
-- Obsolete Column will be removed after this step
</code></pre>

<blockquote>
  <p>Msg 207, Level 16, State 1, Line 4
  Invalid column name 'ObsoleteColumn'.</p>
</blockquote>

<p>Both scenarios are basically the same right? Only do the migration to the new structure if the obsolete Table/Column exists. Otherwise ignore.</p>

<p><strong>If I execute scenario one while ObsoleteTable is already removed. it won't fail. So why does scenario 2 fail?</strong> </p>