locked
Adding a value to a new column RRS feed

  • Question

  • I am adding a new column to a table. I would like to conditionally populate that new column, but the script won't recognize the new column name until after it is added.

    I want to do something like this:

    If col_length('myTable','newColumn') is null

    Begin

    Alter myTable add newColumn money not null default 0

    update myTable set newColumn=anOldColumn where oldBool=1

    end

    So if newColumn does not exist in myTable, newColumn gets added with a value of 0. Then, if oldBool is true, newColumn is set to the value of oldColumn.

    I only want to do the update if newColumn is being added; if it already exists, don't add it and don't update it.

    But at the point when I try to execute this, newColumn does not exist, and the script fails the syntax check and won't run.

    Ideas?

    Monday, July 29, 2013 10:43 PM

Answers

  • One way

    If col_length('myTable','newColumn') is null
    Begin
      Alter Table myTable add newColumn money not null default 0
      Exec ('update myTable set newColumn=anOldColumn where oldBool=1')
    end

    Tom

    • Proposed as answer by Naomi N Monday, July 29, 2013 11:32 PM
    • Marked as answer by Davis Farmer Tuesday, July 30, 2013 1:13 PM
    Monday, July 29, 2013 11:26 PM

All replies

  • One way

    If col_length('myTable','newColumn') is null
    Begin
      Alter Table myTable add newColumn money not null default 0
      Exec ('update myTable set newColumn=anOldColumn where oldBool=1')
    end

    Tom

    • Proposed as answer by Naomi N Monday, July 29, 2013 11:32 PM
    • Marked as answer by Davis Farmer Tuesday, July 30, 2013 1:13 PM
    Monday, July 29, 2013 11:26 PM
  • try this,

    If col_length('myTable','newColumn') is null
    
    Begin
    
    Alter table myTable add  newColumn money not null default 0
    
    update myTable set newColumn=anOldColumn where oldBool=1
    
    end


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, July 30, 2013 2:48 AM
  • Thanks, Tom. That worked swell.
    Tuesday, July 30, 2013 1:12 PM
  • Umm, what? Change the colors? ;)
    Tuesday, July 30, 2013 1:14 PM