Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

altering table

Posted on 2011-09-19
4
Medium Priority
?
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 60

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 60

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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview

610 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