Stack Overflow
Stack Overflow

Questions tagged [alter-column]

Alter column is a part of sql's Alter table statement used to change an existing column in a table.

Alter column can be used to set a default value, changing the column's data type, or changing the column's maximum length.

Alter column is supported in Microsoft Access and in Sql Server. To modify columns properties in Oracle, for example, you will need to use Modify column instead.

Alter column related tags

62 questions
1360
votes
14 answers

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must be preserved. I am looking…
Karmic Coder
  • 17,569
  • 6
  • 32
  • 42
257
votes
3 answers

How to alter a column's data type in a PostgreSQL table?

Entering the following command into a PostgreSQL interactive terminal results in an error: ALTER TABLE tbl_name ALTER COLUMN col_name varchar (11); What is the correct command to alter the data type of a column?
tree em
  • 20,379
  • 30
  • 92
  • 130
216
votes
13 answers

How to ALTER multiple columns at once in SQL Server

I need to ALTER the data types of several columns in a table. For a single column, the following works fine: ALTER TABLE tblcommodityOHLC ALTER COLUMN CC_CommodityContractID NUMERIC(18,0) But how do I alter multiple columns in one statement?…
D.mahesh
  • 2,169
  • 2
  • 12
  • 3
209
votes
7 answers

Alter column, add default constraint

I have a table and one of the columns is "Date" of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect syntax near…
ram
  • 11,468
  • 16
  • 63
  • 89
92
votes
5 answers

When increasing the size of VARCHAR column on a large table could there be any problems?

I'm using SQL Server 2008 and I need to make a VARCHAR field bigger, from (200 to 1200) on a table with about 500k rows. What I need to know is if there are any issues I have not considered. I will be using this TSQL statement: ALTER TABLE…
Paul T Davies
  • 2,527
  • 2
  • 22
  • 39
90
votes
5 answers

Change a Nullable column to NOT NULL with Default Value

I came across an old table today with a datetime column called 'Created' which allows nulls. Now, I'd want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()). So far I've got the following…
Jim B
  • 8,344
  • 10
  • 49
  • 77
55
votes
3 answers

ALTER TABLE on dependent column

I am trying to alter column datatype of a primary key to tinyint from int.This column is a foreign key in other tables.So,I get the following error: Msg 5074, Level 16, State 1, Line 1 The object 'PK_User_tbl' is dependent on column 'appId'. Msg…
Milee
  • 1,191
  • 1
  • 11
  • 29
19
votes
3 answers

Change a column to not allow nulls

So I want to change a column in my SQL Server database to not allow nulls, but I keep getting an error. this is the sql statement I am using: alter table [dbo].[mydatabase] alter column WeekInt int not null and this is the error I am getting : Msg…
user2255811
  • 496
  • 2
  • 8
  • 19
16
votes
3 answers

SQL: ALTER COLUMN to shorter CHAR(n) type

I'm working with MS SQL SERVER 2003. I want to change a column in one of my tables to have fewer characters in the entries. This is identical to this question: Altering a Table Column to Accept More Characters except for the fact that I want fewer…
Vivian River
  • 31,198
  • 62
  • 198
  • 313
10
votes
1 answer

How do I change a column from null to not null when an index depends on that column without recreating the index?

I have a column Column which is declared to be NULL DEFAULT(GETUTCDATE()) and there's a non-clustered index that includes this column. I want to change this column to be NOT NULL DEFAULT(GETUTCDATE()) and when I run ALTER TABLE ALTER COLUMN…
sharptooth
  • 167,383
  • 100
  • 513
  • 979
8
votes
2 answers

SQL SET DEFAULT not working in MS Access

Possible Duplicate: DEFAULT clause in ALTER TABLE statement resulting in syntax error I am trying to execute the following statement using a SQL query within MS Access; ALTER TABLE [table] ALTER COLUMN [column] SET DEFAULT 'default…
Steztric
  • 2,832
  • 2
  • 24
  • 43
4
votes
2 answers

MSSQL: alter column not null, in general

In Microsoft SQL Server 2008 R2, I would like to change a nullable column to not null. Obviously, I could do this by restating the datatype, as alter table t alter column c int not null if the column t.c is of int datatype, for example. But what…
Ed Avis
  • 1,350
  • 17
  • 36
3
votes
1 answer

ALTER COLUMN TYPE from tinyInt to Varchar in Mysql

I need to change column type from tinyInt(used as bool) to Varchar, without loosing data. I have found many answers on stack-overflow but all of them are written in postgres and I have no idea how to rewrite it in Mysql. Answers for this problem on…
Thamiar
  • 600
  • 7
  • 22
3
votes
1 answer

Convert postgresql column from character varying to integer

I'm trying to change a column type from "character varying(15)" to an integer. If I run "=#SELECT columnX from tableY limit(10);" I get back: columnX ---------- 34.00 12.00 7.75 18.50 4.00 11.25 18.00 16.50 If i run "=#\d+ columnX" i get back: …
bk201
  • 346
  • 2
  • 5
  • 14
2
votes
2 answers

Behind the scene operations for ALTER COLUMN statement in SQL Server

I am altering the column datatype for a table with around 100 Million records using the below query: ALTER TABLE dbo.TARGETTABLE ALTER COLUMN XXX_DATE DATE The column values are in the right date format as I inserted original date from a valid data…
George Xu
  • 69
  • 3
1
2 3 4 5