246

I'm trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I've dropped and re-created tables before but I've never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: ALTER TABLE (Transfact-SQL) however I can't make heads or tails of it.

I have the following so far (essentially nothing unfortunately):

alter table [progennet_dev].PROGEN.LE
alter column UR_VALUE_3

How do I approach this? Is there better documentation for this statement out there (I did some searches for an example statement but came up empty)?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Michael A
  • 9,480
  • 22
  • 70
  • 114
  • In Microsoft SQL Server Management Studio, you can also right-click a table and select "Design" to open the design view. From here you are presented with a list of each column in the table, with each column's respective data type listed next to it. You can modify the value here. However, this method will drop the table and rebuild/readd it, IIRC. – TylerH Sep 03 '21 at 21:04

10 Answers10

472

You need 

ALTER TABLE YourTable ALTER COLUMN YourColumn <<new_datatype>> [NULL | NOT NULL]

But remember to specify NOT NULL explicitly if desired. 

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NOT NULL;

If you leave it unspecified as below...

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500);

Then the column will default to allowing nulls even if it was originally defined as NOT NULL. i.e. omitting the specification in an ALTER TABLE ... ALTER COLUMN is always treated as.

ALTER TABLE YourTable ALTER COLUMN YourColumn VARCHAR (500) NULL;

This behaviour is different from that used for new columns created with ALTER TABLE (or at CREATE TABLE time). There the default nullability depends on the ANSI_NULL_DFLT settings.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Could you explain the purpose of adding '[NOT] NULL' here? Just curios why your answer had this an Mitch's doesn't? – Michael A Jan 12 '12 at 01:40
  • @MitchWheat - ["ANSI_NULL defaults are always on for ALTER COLUMN; if not specified, the column is nullable."](http://msdn.microsoft.com/en-us/library/ms190273.aspx) – Martin Smith Jan 12 '12 at 01:44
  • So [NOT NULL] specifies that NULL values -cannot- be placed in the column? – Michael A Jan 12 '12 at 02:00
  • 4
    @Michael - Without the square brackets, yes. The square brackets indicate that bit of syntax is optional. If you omit it allowing `NULL` is the default. – Martin Smith Jan 12 '12 at 02:04
  • have to say i keep trying this and continue to get the error Error code 30000, SQL state 42X01: Syntax error: Encountered "varchar" at line 2, column 20. – DCX Jun 09 '15 at 15:12
  • @Kdean571 - Seems like you are using Derby from that error message. This syntax is SQL Server. – Martin Smith Jun 09 '15 at 15:19
  • 3
    sorted it :)..ALTER COLUMN MY_COL SET DATA TYPE VARCHAR(100) – DCX Jun 09 '15 at 15:26
28

Increasing column size with ALTER will not lose any data:

alter table [progennet_dev].PROGEN.LE 
    alter column UR_VALUE_3 varchar(500)

As @Martin points out, remember to explicitly specify NULL | NOT NULL

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
27

For MySQL or DBMSes other than MSSQL, you may need to use modify instead of alter for the column value:

ALTER TABLE `table name` 
MODIFY COLUMN `column name` varchar("length");

For MSSQL:

ALTER TABLE `table name` 
ALTER COLUMN `column name` varchar("length");
fasantos
  • 179
  • 1
  • 3
  • 12
anonymous
  • 289
  • 3
  • 3
  • 4
    That's what was already posted. Is there something new you'd like to share? – Nico Haase Mar 28 '18 at 09:58
  • 1
    Also it works for MySQL and this question is tagged [TSQL] so it's in the wrong place – jean Mar 28 '18 at 10:55
  • 8
    MODIFY is what's new. ALTER COLUMN gave me a syntax error but MODIFY COLUMN worked for me with no problems. – KGBird Dec 21 '18 at 18:25
  • 2
    @KGBird - because you aren't using SQL Server. `MODIFY` is syntax for some other database system than the question was about – Martin Smith Dec 17 '19 at 17:06
10
ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME VARCHAR(40);

I am using Oracle SQL Developer and @anonymous' answer was the closest, but kept receiving syntax errors until I edited the query to this. I changed alter to modify and there's no need to define column_name as column.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Jett
  • 781
  • 1
  • 14
  • 30
3

Using Maria-DB and DB-Navigator tool inside IntelliJ, MODIFY Column worked for me instead of Alter Column

Rahul Sharma
  • 31
  • 3
2

In Oracle SQL Developer

ALTER TABLE car_details MODIFY torque VARCHAR(100);

Biman Pal
  • 391
  • 4
  • 9
0

I was also having above doubt, what worked for me is 

ALTER TABLE `your_table` CHANGE `property` `property` 
VARCHAR(whatever_you_want) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
R. Gurung
  • 1,356
  • 1
  • 14
  • 34
0

This worked for me in db2:

alter table "JOBS"  alter column "JOB_TITLE" set  data type varchar(30);
Mukesh
  • 21
  • 1
  • 3
0

As an alternative, you can save old data and create a new table with new parameters.

see image

In SQL Server Management Studio: "your database" => task => generatescripts => select specific database object => "your table" => advanced => types of data to script - schema and data => generate

Personally, I did so.

Stephan Bauer
  • 9,120
  • 5
  • 36
  • 58
vlad
  • 11
  • 2
-1

For MariaDB, use modify column:

ALTER TABLE table_name MODIFY COLUMN column_name VARCHAR (500);

It will work.

hammadshahir
  • 350
  • 4
  • 7