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
What Did Mongodb Not Being Acid Compliant Before V4 Really Mean
How to Check for Is Not Null and Is Not Empty String in SQL Server
How to Drop a Foreign Key Constraint Only If It Exists in SQL Server
How to Identify All Stored Procedures Referring a Particular Table
With (Nolock) VS Set Transaction Isolation Level Read Uncommitted
Why Is Postgresql Not Using My Indexes on a Small Table
Insert Multiple Records in Oracle
Limit Characters Returned in Oracle SQL Query
From What Do SQL Parameters Protect You
How to Loop Through a Table Variable in T-Sql
SQL Server - Possible Pivot Solution
What Is the Purpose of Order by 1 in SQL Select Statement
Copy Table Structure into New Table
Connect SQL Server in Ruby on Rails
Change Schema Name of Table in SQL
Rails 3 Activerecord Query Using Both SQL in and SQL or Operators