MS SQL Server, Setting Default Value to value of another column

Setting Default Value to value of another column

Author	Message
Owen Mortense #1 / 6	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

Anith Se #2 / 6	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

Owen Mortense #3 / 6	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

BP Margoli #4 / 6	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

Owen Mortense #5 / 6	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

Anith Se #6 / 6	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

Page 1 of 1

[ 6 post ]