Add new column with default value

  • Hi There,

    I want to set a default value to a column while adding it.

    Create table test(id int )

    insert into Test values(123)

    alter table test add column2 int default(0)

    In the above query I have set the default value, but it will not set the default value to the already inserted columns.

    So how to set a default value for the already inserted rows, while adding a new column to it ?

  • vignesh.ms (4/20/2015)


    Hi There,

    I want to set a default value to a column while adding it.

    Create table test(id int )

    insert into Test values(123)

    alter table test add column2 int default(0)

    In the above query I have set the default value, but it will not set the default value to the already inserted columns.

    So how to set a default value for the already inserted rows, while adding a new column to it ?

    The default value is used for new rows where no other value is specified. If you want to update existing rows, use an update statement.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Use "WITH VALUES" in your default specification:

    alter table test add column2 int default(0) WITH VALUES

    -- Gianluca Sartori

  • spaghettidba (4/20/2015)


    Use "WITH VALUES" in your default specification:

    alter table test add column2 int default(0) WITH VALUES

    Would this overwrite existing non-null values, as that appears to be the requirement?

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • No values will be in the column, since it's being added by the ALTER TABLE command. What do you mean exactly?

    -- Gianluca Sartori

  • spaghettidba (4/20/2015)


    No values will be in the column, since it's being added by the ALTER TABLE command. What do you mean exactly?

    Oops. It's Monday morning and I've clearly not had enough coffee.:blush:

    For some bizarre reason, I was thinking that the requirement was to add a default constraint to an existing column and blitz any existing values in that column.

    Your first answer nails it.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I think he's confused his requirement: he mentions "already inserted rows" and "already inserted columns" almost in the same breath. I think Gianluca's solution will work for him; alternatively he could specify NOT NULL when adding the column.

    John

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply