Altering a table, column is a BIT data type 
Author Message
 Altering a table, column is a BIT data type

I need to alter a table and add a column that is of BIT data type.
When I  tried to alter the table, and specify that the new column could
allow "null" values, the alter didn't work (but the light did go on and
I realized that the column would have to be "not null", binary you know
- off or on).

So, I'm assuming that I have to use "not null" when the column is added
to the existing table.   However, since there's already rows in the
table, how do you preset those existing rows to some value (say FALSE)
and will I be able to add the new table given the fact that there are
rows already that don't have the column set to TRUE or FALSE?

Opinions expressed herein are my own and may not represent those of my employer.



Sun, 27 Aug 2000 03:00:00 GMT
 Altering a table, column is a BIT data type

I need to alter a table and add a column that is of BIT data type.
When I  tried to alter the table, and specify that the new column could
allow "null" values, the alter didn't work (but the light did go on and
I realized that the column would have to be "not null", binary you know
- off or on).

So, I'm assuming that I have to use "not null" when the column is added
to the existing table.   However, since there's already rows in the
table, how do you preset those existing rows to some value (say FALSE)
and will I be able to add the new table given the fact that there are
rows already that don't have the column set to TRUE or FALSE?

Opinions expressed herein are my own and may not represent those of my employer.



Sun, 27 Aug 2000 03:00:00 GMT
 Altering a table, column is a BIT data type

I need to alter a table and add a column that is of BIT data type.
When I  tried to alter the table, and specify that the new column could
allow "null" values, the alter didn't work (but the light did go on and
I realized that the column would have to be "not null", binary you know
- off or on).

So, I'm assuming that I have to use "not null" when the column is added
to the existing table.   However, since there's already rows in the
table, how do you preset those existing rows to some value (say FALSE)
and will I be able to add the new table given the fact that there are
rows already that don't have the column set to TRUE or FALSE?

Opinions expressed herein are my own and may not represent those of my employer.



Sun, 27 Aug 2000 03:00:00 GMT
 Altering a table, column is a BIT data type

Rich,

using "alter table" command, you can't add a bit column and the new
column can only be nullable. sybase will assign null values into the new
column. you can do your changes by dropping and recreating the table
using "create table" or even "select into".

cheers,

raoul

Quote:

> I need to alter a table and add a column that is of BIT data type.
> When I  tried to alter the table, and specify that the new column could
> allow "null" values, the alter didn't work (but the light did go on and
> I realized that the column would have to be "not null", binary you know
> - off or on).

> So, I'm assuming that I have to use "not null" when the column is added
> to the existing table.   However, since there's already rows in the
> table, how do you preset those existing rows to some value (say FALSE)
> and will I be able to add the new table given the fact that there are
> rows already that don't have the column set to TRUE or FALSE?

> Opinions expressed herein are my own and may not represent those of my employer.



Sun, 27 Aug 2000 03:00:00 GMT
 Altering a table, column is a BIT data type

There is no way to add a bit column to a table using the
alter table command because such added columns must allow
null and because bit columns cannot be declared null.

You will have to either use a tinyint as a bit column,
or do something like:

select *, convert(bit,1) as "mybitcolumn"
into newtable from oldtable
go
drop oldtable
go
sp_rename oldtable, newtable
go

and recreate any triggers, indexes, views, procedures, etc
that referenced newtable.

--
Bret Halford                    Imagine my disappointment
Sybase Technical Support        in learning the true nature            
3665 Discovery Drive            of rec.humor.oracle...
Boulder, CO 80303



Sun, 27 Aug 2000 03:00:00 GMT
 Altering a table, column is a BIT data type

Since the bit construct will use a byte of data space any way, and you want
to define a nullable column to begin with, just define the column as a
tinyint
and you will have what you want.

Quote:

>I need to alter a table and add a column that is of BIT data type.
>When I  tried to alter the table, and specify that the new column could
>allow "null" values, the alter didn't work (but the light did go on and
>I realized that the column would have to be "not null", binary you know
>- off or on).

>So, I'm assuming that I have to use "not null" when the column is added
>to the existing table.   However, since there's already rows in the
>table, how do you preset those existing rows to some value (say FALSE)
>and will I be able to add the new table given the fact that there are
>rows already that don't have the column set to TRUE or FALSE?

>Opinions expressed herein are my own and may not represent those of my
employer.



Sun, 27 Aug 2000 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. SQL 6.5 ALTER TABLE statement adding BIT type column

2. ADD bit data type via ALTER TABLE

3. type mismatch error while importing data to a BIT type column

4. 6.5 Cannot alter data type to bit

5. SQL 6.5 ALTER TABLE statement adding BIT type

6. Alter Column Data Type

7. altering data type in a column

8. Data Transfer from Long data type to SQL Server Text data type column

9. Cannot create index on a column of bit data type

10. altering table data types

11. Altering Table Data Types

12. ALTER TABLE - Changing data type (smallint -> int)


 
Powered by phpBB® Forum Software