How to Set a Default Value for One Column in SQL Based on Another 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

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



Leave a reply



Submit