?
Solved

altering table

Posted on 2011-09-19
4
Medium Priority
?
247 Views
Last Modified: 2012-05-12
I want to alter table's column's (x) to change to datatype to bit and set default to 'false'  how can I do that?
0
Comment
Question by:1jaws
  • 2
  • 2
4 Comments
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36564075
1jaws,

You can use, ALTER TABLE ALTER COLUMN and ADD CONSTRAINT.
BOL: http://msdn.microsoft.com/en-us/library/ms190273.aspx

For example, if your column is called Flag1 in the Table1 table:
ALTER TABLE Table1 
ALTER COLUMN Flag1 BIT NOT NULL;
ALTER TABLE Table1 
ADD CONSTRAINT Flag1Dflt 
DEFAULT 0 FOR Flag1;

Open in new window


If you have existing data that fits BIT data type, use WITH VALUES:
ALTER TABLE Table1 
ALTER COLUMN Flag1 BIT NOT NULL;
ALTER TABLE Table1 
ADD CONSTRAINT Flag1Dflt 
DEFAULT 0 FOR Flag1 WITH VALUES;

Open in new window


Kevin
0
 

Author Comment

by:1jaws
ID: 36564226
I am doing second one and it is giving me error saying cannot insert null into tbl1... by the way what is Flag1Dflt means?
0
 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 2000 total points
ID: 36564245
Sorry. I plugged in NOT NULL. If field has NULLs currently and NULLs are allowed then that should be just NULL. Flat1Dflt is name of the CONSTRAINT. You can name that what you want. If you do not want NULLs, then before doing the ALTER statement:

UPDATE Table1 SET Flag1 = 0
WHERE Flag1 IS NULL;

Open in new window

0
 

Author Closing Comment

by:1jaws
ID: 36564250
thank you!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question