Value of a column default to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Value of a column default to another

Author

Topic

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-05-15 : 16:12:51

I'm trying to do this in SQL Server 2000:

Alter TABLE exampleTable
col2 VARCHAR(50) NOT NULL DEFAULT col1

Of course it doesn't work. Any idea how to make it?

Thanks in advance.

George

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 16:23:27

Are you just trying to alter the column? Have you looked at the ALTER TABLE ALTER COLUMN syntax yet?

ALTER TABLE exampleTable
ALTER COLUMN col2 VARCHAR(50) NOT NULL

Not sure about the DEFAULT part though. Give me a minute and I'll see if I can come up with the code to do it.

Tara

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-05-15 : 16:33:16

No, I want to create another column with default value as the first column. Sounds a little weird, but it's my customer's request.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 16:33:36

Here's how to add your default:

ALTER TABLE exampleTable
ADD DEFAULT 'SQLTeam'
FOR col2
WITH VALUES

Not sure how you are going to get col1 as the default though. I put in 'SQLTeam' just to show you what the syntax would be.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 16:35:11

quote:

No, I want to create another column with default value as the first column. Sounds a little weird, but it's my customer's request.

Here's an example from SQL Server Books Online:

F. Add a nullable column with default values
This example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.

ALTER TABLE MyTable
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT getdate() WITH VALUES

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 16:41:10

Why not give him a view of yourTable

CREATE VIEW v_yourTable AS

SELECT Col1, IsNull(Col2,Col1) AS Col2 FROM yourTable

GO

Why store redundant data, and a view will look just like a table..

Brett

8-)

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-05-15 : 17:38:24

We need a column initialized with exact the same value as another column, then we will decide if we want to change the value in the second column.

The first column is for internal use, some value there just make customers confused. So we need another column more meaningful to users. Another reason that we won't be able to change the value in the first column is we already hardcode some of the value in application. I know it doesn't look like a good practice, but right now we don't have much choice :-(

Thanks,

George

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-15 : 17:42:06

Do you really need a default value for this? Why don't you just UPDATE your data using the UPDATE statement, then code your INSERT statements so that it duplicates col1 into col2?

Tara

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-05-15 : 18:08:36

You right. I figure it maybe the only way I can go. Thanks Tara.

quote:

Do you really need a default value for this? Why don't you just UPDATE your data using the UPDATE statement, then code your INSERT statements so that it duplicates col1 into col2?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-16 : 14:53:42

There it is again:

quote:

You right. I figure it maybe the only way I can go. Thanks Tara.

What???? You don't the view idea?


CREATE TABLE myTable (col1 int NOT NULL, col2 int)
GO

ALTER TABLE myTable WITH NOCHECK ADD PRIMARY KEY  CLUSTERED 
	(
		[col1]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

SELECT col1, col2 FROM myTable
GO

INSERT INTO myTable(col1,col2) SELECT 2, 3
INSERT INTO myTable(col1,col2) SELECT 3, null
INSERT INTO myTable(col1,col2) SELECT 4, 2
INSERT INTO myTable(col1,col2) SELECT 1, null

SELECT col1, col2 FROM myTable
GO

CREATE VIEW v_myTable AS SELECT col1, ISNULL(col2,col1) as col2 FROM myTable
GO

SELECT col1, col2 FROM v_myTable
GO

DROP VIEW v_myTable

DROP TABLE myTable
GO

Brett

8-)

ygeorge
Yak Posting Veteran

68 Posts

Posted - 2003-05-16 : 18:11:45

oh yeah...this is a good idea. thanks Brett.

quote:

There it is again:

quote:

You right. I figure it maybe the only way I can go. Thanks Tara.

What???? You don't the view idea?


CREATE TABLE myTable (col1 int NOT NULL, col2 int)
GO

ALTER TABLE myTable WITH NOCHECK ADD PRIMARY KEY  CLUSTERED 
	(
		[col1]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

SELECT col1, col2 FROM myTable
GO

INSERT INTO myTable(col1,col2) SELECT 2, 3
INSERT INTO myTable(col1,col2) SELECT 3, null
INSERT INTO myTable(col1,col2) SELECT 4, 2
INSERT INTO myTable(col1,col2) SELECT 1, null

SELECT col1, col2 FROM myTable
GO

CREATE VIEW v_myTable AS SELECT col1, ISNULL(col2,col1) as col2 FROM myTable
GO

SELECT col1, col2 FROM v_myTable
GO

DROP VIEW v_myTable

DROP TABLE myTable
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources