Setting Default Value to value of another column 
Author Message
 Setting Default Value to value of another column
I've got a database table with some existing data.  I'd like to add another
column, not allowing NULLs.  When the column is added, I want its value to
be equal to the value of another column (TypeID being the existing column,
TypeID2 being the new column).

Anyone know if there is a way in the management console (design table) to do
this?  Or is there a "modify table" command to do it?

TIA,
Owen



Sat, 25 Jun 2005 04:52:35 GMT
 Setting Default Value to value of another column

You can add it as a computed column like:

ALTER TABLE tbl ADD TypeID AS TypeID2

--
- Anith
(Please respond only to newsgroups)



Sat, 25 Jun 2005 05:09:52 GMT
 Setting Default Value to value of another column
Ahhh. No. I need the DEFAULT to be set to what the existing column is.
Later, the values may be changed. So the two columns have to be independent.

Owen


Quote:
> You can add it as a computed column like:

> ALTER TABLE tbl ADD TypeID AS TypeID2

> --
> - Anith
> (Please respond only to newsgroups)



Sat, 25 Jun 2005 05:21:05 GMT
 Setting Default Value to value of another column
Owen,

If I understand you correctly, you have an existing table. You wish to add a
new column to the table, with the NON NULL property, and initialize the
value to that of an existing column. However, I'm assuming, since it makes
no sense for two columns to always have the same value, that the values of
this new column will change over time.

Short answer: No way to do that.

Longer answer:

Option 1) Use ALTER TABLE ... to add the new column with the NON NULL
property, but give it a default value. Then use the UPDATE command to change
the actual values of the new column, and again use ALTER TABLE to drop the
default value of the new column.

Option 2) Use ALTER TABLE ... to add the new column with the NULL property.
Use the UPDATE column to change the actual values of the new column. Use the
ALTER TABLE to change the NULLability of the new column.

Personally, I'd recommend the second option ... I think it will execute
faster.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:
> I've got a database table with some existing data.  I'd like to add
another
> column, not allowing NULLs.  When the column is added, I want its value to
> be equal to the value of another column (TypeID being the existing column,
> TypeID2 being the new column).

> Anyone know if there is a way in the management console (design table) to
do
> this?  Or is there a "modify table" command to do it?

> TIA,
> Owen



Sat, 25 Jun 2005 05:23:47 GMT
 Setting Default Value to value of another column
That's what I was afraid of.  The new column makes sense to be the same as
the old one on original insert, but most of the time it will change later.
I was hoping not to have to go and modify all the old code right off the
bat.

Thanks,
Owen


Quote:
> Owen,

> If I understand you correctly, you have an existing table. You wish to add
a
> new column to the table, with the NON NULL property, and initialize the
> value to that of an existing column. However, I'm assuming, since it makes
> no sense for two columns to always have the same value, that the values of
> this new column will change over time.

> Short answer: No way to do that.

> Longer answer:

> Option 1) Use ALTER TABLE ... to add the new column with the NON NULL
> property, but give it a default value. Then use the UPDATE command to
change
> the actual values of the new column, and again use ALTER TABLE to drop the
> default value of the new column.

> Option 2) Use ALTER TABLE ... to add the new column with the NULL
property.
> Use the UPDATE column to change the actual values of the new column. Use
the
> ALTER TABLE to change the NULLability of the new column.

> Personally, I'd recommend the second option ... I think it will execute
> faster.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.


> > I've got a database table with some existing data.  I'd like to add
> another
> > column, not allowing NULLs.  When the column is added, I want its value
to
> > be equal to the value of another column (TypeID being the existing
column,
> > TypeID2 being the new column).

> > Anyone know if there is a way in the management console (design table)
to
> do
> > this?  Or is there a "modify table" command to do it?

> > TIA,
> > Owen



Sat, 25 Jun 2005 05:27:20 GMT
 Setting Default Value to value of another column
You cannot. Columns cannot be set as a DEFAULT. If it helps, you
can create the column & simply do an UPDATE like:

UPDATE tbl
   SET TypeID2 = TypeID

Or for new INSERTs you can create a trigger (inelegant & overkill!)
like:

UPDATE tbl
   SET TypeID2 = inserted.TypeID
  FROM inserted
 WHERE tbl.key = inserted.key

--
- Anith
(Please respond only to newsgroups)



Sat, 25 Jun 2005 05:30:16 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Use ISNULL to set the value to the default value when NULL

2. How to set default value of combo box to value of record in database

3. Setting up default values from the passed value

4. current value of default value in column?

5. Setting Default Value for ADOX 2.5 Column

6. Set column name as default value

7. programmatically set access column default value?

8. error setting default column value - HELP!!

9. Setting a column's default value with ADOX

10. how to set default value for column using script

11. Set default value for datetime column

12. How to set a column not using default value


 
Powered by phpBB® Forum Software