February 27, 2008 at 11:05 am
iam having a problem,
iam managing a database with over 150,000 records
now iam getting erros becoz of null problem
i want to set the column default to 0
then column type is int
alter table candreg set totalexp default=0
is it something like that
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 27, 2008 at 11:13 am
February 27, 2008 at 11:14 am
Forgot to put not null
alter table candreg
alter column totalexp int default 0 not null
February 27, 2008 at 11:39 am
it says
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 27, 2008 at 11:49 am
It's SET DEFAULT. See "Alter Table" in BOL.
Greg
February 27, 2008 at 11:52 am
i tried that also but
it says
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'set'.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 27, 2008 at 11:58 am
my table column doesnt have default now, it allows null value, i want to it to not allow nulls, but have 0 value instead
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 27, 2008 at 11:59 am
sukhoi971 (2/27/2008)
iam having a problem,iam managing a database with over 150,000 records
now iam getting erros becoz of null problem
i want to set the column default to 0
then column type is int
alter table candreg set totalexp default=0
is it something like that
You can set a default with something like:
Alter Table CandReg add constraint
DF_totalexp default 0 for totalexp
You can also use the gui in SSMS is you are using 2005.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 27, 2008 at 12:02 pm
Okay lets get formal here
ALTER TABLE dbo.candreg
ADD CONSTRAINT DF_candreg_totalexp DEFAULT 0 FOR totalexp
February 27, 2008 at 12:21 pm
what about not allowing nulls, cannot set via gui it has over 150,000 records
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
February 27, 2008 at 12:40 pm
SO U HAVE 15K ROWS IN WHICH A COLUMN ALLOWS NULL AND U JUS WANT TO MAKE IT AS NOT NULL WITH A DEFAULT VALUE OF 0 IS THAT THE ONE UR ASKING .......I MEAN U WANT THAT COLUMN VALUE TO BE 0 ...IF SO LET ME KNOW ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 12:41 pm
Add the constraint, I posted. Then alter the column to not null.
February 27, 2008 at 12:49 pm
WHAT ADAM HAS SAID WRKS PREFECTLY..
ID INT NULL
NAME CHAR NULL
SELECT * FROM DBO.TEST
ID NAME
----------- ----------
2121 DWSD
12 jnjc
9 JJ
323 NNMB
23923 NBH
9213 NBN2
9239 DB
ALTER TABLE dbo.TEST
ADD CONSTRAINT DF_TEST_ID DEFAULT 0 FOR ID
SELECT * FROM DBO.TEST
ID NAME
----------- ----------
2121 DWSD
12 jnjc
9 JJ
323 NNMB
23923 NBH
9213 NBN2
9239 DB
0 FF
77 NB
0 BN
0 JJD
0 J
9 HHJ
(13 row(s) affected)
SO U JUS GET 0 AS DEFAULT WHEN U JUS DNT IVE ANY VALUE ,....SOO IF U WANT TO MODIFY AS NOT NULL
JUS ROGHT CLICK ON THE TABLE AND CLICK MODIFY AND JUS UNCHECK THE NULL BOX...THATS IT THKZ ADAM...
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
February 27, 2008 at 1:27 pm
If you are still having a problems. You can also manually update the table. This will limit the problems you encounter in trying to change the column definition from NULL to NOT NULL.
Update dbo.candreg
set totalexp = 0
where totalexp is null
February 27, 2008 at 1:41 pm
U CAN UPDATE THAT WAY ONLY WHEN THERE ARE NO MORE ROWS TO BE INSERTED IF NOT EVERYTIME U INSERT SOME ROWS U NEED TO UPDATE THE COLUMN .....SO IT JUS APPLIES TO THE EXISTING ROWS...NOT FOR THE COLUMN PERMANENTLY..BETTR U JUS FOLLOW THE CONSTRAINTS IT WRKS .....PLZ LET ME KNOW ....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply