Add a Column to a Table with a Default Value Equal to the Value of an Existing Column

Add a column to a table with a default value equal to the value of an existing column

Try this:

ALTER TABLE tablename ADD newcolumn type NOT NULL DEFAULT (0)
Go
Update tablename SET newcolumn = oldcolumn Where newcolumn = 0
Go

Add a column with a default value to an existing table in SQL Server

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:

If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate

    a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:

The WITH VALUES is only needed when your Column is Nullable

    and you want the Default Value used for Existing Records.

If your Column is NOT NULL, then it will automatically use the Default Value

    for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:

If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.

If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),

    then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.

Special Thanks to:

    @Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Add a column to a table with a default value equal to the value of an existing column in HBase through Apache Phoenix

You can use this statements to create a column and update it based on value from another one:

ALTER TABLE products ADD C DECIMAL(20,12);
UPSERT INTO products(your_key, C) select your_key, B from products;

Adding a new SQL column with a default value

Try this:

ALTER TABLE table1 ADD COLUMN foo INT DEFAULT 0;

From the documentation that you linked to:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

alter_specification:
...
ADD [COLUMN] (col_name column_definition,...)
...

To find the syntax for column_definition search a bit further down the page:

column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 12.1.17, “CREATE TABLE Syntax”.

And from the linked page:

column_definition:  
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]

Notice the word DEFAULT there.

How to add MySQL table column with a default value of another column of the existing row

It is not possible as default column. You can write a trigger and do that or add virtual column in Mysql 5.7.

OR

alter table Tab1 add allocated_amount int;  -- Add column
update Tab1 set allocated_amount= amount; -- Set the value

Or you could create a Virtual Column:

alter table Table1 
add allocated_amount integer GENERATED ALWAYS AS (amount) VIRTUAL;

Clickhouse - add a new column with the default value being based off another column

DEFAULT may accept expression not only static value:

ALTER TABLE table1 ADD COLUMN C String DEFAULT A

Laravel Migration: Add a column with a default value of an existing column

This should work:

Schema::table('users', function(Blueprint $table) {
$table->dateTime('last_activity');
});

\DB::statement('UPDATE users SET last_activity = updated_at');

See "Running a General Statement": https://laravel.com/docs/5.1/database#database-transactions

To enforce this relationship when a User is created or updated, you can use Model Events and/or Model Observers.



Related Topics



Leave a reply



Submit