title: Test Column exists, Add Column, and Update Column
body: <p>I'm trying to write a SQL Server database update script. I want to test for the existence of a column in a table, then if it doesn't exist add the column with a default value, and finally update that column based on the current value of a different column in the same table. I want this script to be runnable multiple times, the first time updating the table and on subsequent runs the script should be ignored. My script currently looks like the following:</p>
  
  <pre><code>IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_NAME = 'PurchaseOrder' AND COLUMN_NAME = 'IsDownloadable')
  BEGIN
  
  ALTER TABLE [dbo].[PurchaseOrder] ADD [IsDownloadable] bit NOT NULL DEFAULT 0
  
  UPDATE [dbo].[PurchaseOrder] SET [IsDownloadable] = 1 WHERE [Ref] IS NOT NULL
  
  END
  </code></pre>
  
  <p>SQL Server returns error "Invalid column name 'IsDownloadable'", i.e. I need to commit the DDL before I can update the column. I've tried various permutations but I'm getting nowhere fast.</p>