Modify the column data type.

Modify the column data type.

Author: David _zhang @ sh: [mark the article in hyperlink form when reprinting]

Link: http://www.cnblogs.com/david-zhang-index/archive/2012/04/10/2441015.html

Field operations	Procedure
Update field name	Alter table TABLE_NAME rename column column_old to column_new;
Add Field	Alter table TABLE_NAME add COLUMN_NAME varchar (10 );
Delete Field	Alter table TABLE_NAME drop column COLUMN_NAME;
Add fields with values	Alter table TABLE_NAME ADD COLUMN_NAME NUMBER (1) DEFAULT 1;
Modify Field Value	Update TABLE_NAME set filedname = value where filedname = value;
Modify Field Data Type	Alter table tablename modify filedname varchar2 (20 );

1 SQL> select * from v$version;2 3 BANNER4 --------------------------------------------------------------------------------5 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production6 PL/SQL Release 11.2.0.1.0 - Production7 CORE    11.2.0.1.0      Production8 TNS for Linux: Version 11.2.0.1.0 - Production9 NLSRTL Version 11.2.0.1.0 - Production

1. When modifying the field data type, if the column has data, it will report the ORA-01439: column to be modified must be empty to change datatype, at this time you need to use another method to modify:

 1 SQL> alter table zyt add id_temp varchar2(10); 2  3 Table altered. 4  5 SQL> commit; 6  7 Commit complete. 8  9 SQL> select * from zyt;10 11 NAME               ID ID_TEMP12 ---------- ---------- ----------13 zyt1                114 david               215 16 SQL> alter table zyt rename column id to id_bak;17 18 Table altered.19 20 SQL> select * from zyt;21 22 NAME           ID_BAK ID_TEMP23 ---------- ---------- ----------24 zyt1                125 david               226 27 SQL> desc zyt;28  Name                                      Null?    Type29  ----------------------------------------- -------- ----------------------------30  NAME                                               VARCHAR2(10)31  ID_BAK                                    NOT NULL NUMBER(2)32  ID_TEMP                                            VARCHAR2(10)33 34 SQL> update zyt set ID_TEMP = cast(ID_BAK as varchar2(10));35 36 2 rows updated.37 38 SQL> commit;39 40 Commit complete.41 42 SQL> select * from zyt;43 44 NAME           ID_BAK ID_TEMP45 ---------- ---------- ----------46 zyt1                1 147 david               2 248 49 SQL>  alter table zyt drop column ID_BAK;50 51 Table altered.52 53 SQL> commit;54 55 Commit complete.56 57 SQL> select * from zyt;58 59 NAME       ID_TEMP60 ---------- ----------61 zyt1       162 david      263 64 SQL> desc zyt;65  Name                                      Null?    Type66  ----------------------------------------- -------- ----------------------------67  NAME                                               VARCHAR2(10)68  ID_TEMP                                            VARCHAR2(10)

Note: This method can meet the requirements. Because new fields are added to the end of the table by default, row migration may occur, which may affect the application and involve complex data. This is not the best method.

2. Create an intermediate stepping stone to temporarily store data

 1 SQL> desc zyt; 2  Name                                      Null?    Type 3  ----------------------------------------- -------- ---------------------------- 4  NAME                                               VARCHAR2(10) 5  ID                                                 VARCHAR2(10) 6  7 SQL> select * from zyt; 8  9 NAME       ID10 ---------- ----------11 zyt1       112 david      213 14 SQL> alter table zyt add id_temp VARCHAR2(10)15 16 Table altered.17 18 SQL> select * from zyt;19 20 NAME       ID            ID_TEMP21 ---------- ---------- ----------22 zyt1       123 david      224 25 SQL> update zyt set ID_TEMP=id,id=null;26 27 2 rows updated.28 29 SQL> select * from zyt;30 31 NAME       ID            ID_TEMP32 ---------- ---------- ----------33 zyt1                           134 david                          235 36 SQL> alter table zyt modify id number(10);37 38 Table altered.39 40 SQL> desc zyt;41  Name                                      Null?    Type42  ----------------------------------------- -------- ----------------------------43  NAME                                               VARCHAR2(10)44  ID                                                 NUMBER(10)45  ID_TEMP                                            VARCHAR2(10)46 47 SQL> update zyt set id=ID_TEMP,ID_TEMP=null;48 49 2 rows updated.50 51 SQL> select * from zyt;52 53 NAME               ID    ID_TEMP54 ---------- ---------- ----------55 zyt1                156 david               257 58 SQL> alter table zyt drop column ID_TEMP;59 60 Table altered.61 62 SQL> commit;63 64 Commit complete.65 66 SQL> select * from zyt;67 68 NAME               ID69 ---------- ----------70 zyt1                171 david               272 73 SQL> desc zyt;74  Name                                      Null?    Type75  ----------------------------------------- -------- ----------------------------76  NAME                                               VARCHAR2(10)77  ID                                                 NUMBER(10)

Note: The second method is to add a column of the same type as the modified column, and then copy the data of the modified column to the new column and leave the column to be modified empty, then, modify the data type and copy the data from the new column. This process involves two data copies. If there is a large amount of data, it will be slow and generate a lot of undo and redo; the advantage is that row migration does not occur for data.

How can I change the column data type in SQL server 2005?

Alter table name alter column New Data Type

Example:

Alter table my_name alter column id varchar (20)

How can I change the field data type?

Alert table name
Alter column field Name field type

For example, Modify Field a of table t1 to be of the text type.
Alter table t1
Alter column a text (50)

You can also select a table, right-click it, and choose modify from the shortcut menu. Modify the table in the designer.