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
How to set a default value for one column in SQL based on another column
I would use a trigger on Insert.
Just check to see if a value has been assigned, and if not, go grab the correct one and use it.
Set the default value of a column based on another column of a different data type
I don't think you can set the DEFAULT value of one column based on another column using the DEFAULT CONSTRAINT.
You may want to consider a computed column if all you're looking to do is replace a NULL value. http://msdn.microsoft.com/en-us/library/ms188300.aspx
In extreme cases, you can do exactly what you want in a TRIGGER. Read up on INSTEAD OF INSERT triggers. http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx
Can the default value of one column be another column in SQL?
A common solution is to create a view for displaying the data. In the view you compute the description. Like this
CREATE TABLE t
(name VARCHAR2(10),
description VARCHAR2(10));
CREATE OR REPLACE VIEW t_v (name, description)
AS
SELECT name, NVL(description, name) FROM t;
INSERT into t (name, description) VALUES ('CAR','cool car');
INSERT into t (name, description) VALUES ('BIKE',NULL);
SELECT * FROM t_v;
NAME DESCRIPTIO
---------- ----------
CAR cool car
BIKE BIKE
Set default value of a column to another column's value in CREATE statement?
The documentation is quite clear that the default value cannot refer to other columns:
DEFAULT default_expr
The
DEFAULT
clause assigns a default data value for the column whose
column definition it appears within. The value is any variable-free
expression (subqueries and cross-references to other columns in the
current table are not allowed). The data type of the default
expression must match the data type of the column.The default expression will be used in any insert operation that does
not specify a value for the column. If there is no default for a
column, then the default is null.
Hence, you cannot do what you want with a DEFAULT
clause.
No `trigger` to set column's default value from another column in the same table
You could create that column as a computed column instead of giving it a default:
CREATE TABLE temp_T (col1 INT, col2 AS col1 * 2 PERSISTED);
Now, if you insert the values:
INSERT INTO temp_T (col1) VALUES (7);
you do get 7 in col1
and 14 in col2
.
But this is now a computed column, meaning you cannot ever insert some other value into it - it's always going to be twice the value in col1
. Not sure if that would work in your scenario - but as the error says: you cannot use another column in a default expression for a column.
SQL Server 2017 add new column with default value from another column in the same table
The error clearly tells you: if you add a column with NOT NULL
to a table that already has data in it, you MUST include a DEFAULT
clause to define the default values for the newly added column - you are not doing that....
So try this:
ALTER TABLE [dbo].[TableA]
ADD [Weight2] FLOAT NOT NULL
CONSTRAINT DF_TableA_Weight2 DEFAULT(0);
and then you can update Weight2
to get the same values as in Weight1
:
UPDATE dbo.TableA
SET Weight2 = Weight1
How to default column to default value / value of another column if no matching row in SQL Left Join
Would this do it?
SELECT
a.type_and_cargo,
a.name
b.type_and_cargo,
CASE
WHEN b.type_and_cargo IS NULL THEN a.type_and_cargo
ELSE b.type
END as type,
CASE
WHEN b.type_and_cargo IS NULL THEN 'Unknown'
ELSE b.cargo
END as cargo
FROM table_A a
LEFT JOIN table_B b USING (type_and_cargo)
Related Topics
Are Multiple Joins Unavailable When Using Adodb to Query an Excel File in a Vba Procedure
Cast Collation of Nvarchar Variables in T-Sql
Error: Subquery Must Return Only One Column
Select The Rows That Just Inserted
MySQL Count Data for Last 7 Days
Postgres on Conflict Do Update on Composite Primary Keys
What Is Srid 0 for Geometry Columns
Performance Difference Between Primary Key and Unique Clustered Index in SQL Server
How to Trim All Data in a Select * from Statement
What Is The Limit of The Field Type Bigint in Sql
How to Add Months to a Current_Timestamp in Sql
Oracle Text Search on Multiple Tables and Joins
Create a Trigger to Insert Records from a Table to Another One. Get Inserted Values in a Trigger
Can You Use Auto-Increment in MySQL with Out It Being The Primary Key
Sql Query for Time In/Out Attendance