ITPub博客

首页 > Linux操作系统 > Linux操作系统 > How does one add a column to the middle of a table(ZT)

How does one add a column to the middle of a table(ZT)

原创 Linux操作系统 作者:lwitpub 时间:2011-12-01 20:42:05 0 删除 编辑
For example, in MySQL the following syntax is valid:

ALTER TABLE tablename ADD columnname AFTER columnname;

Oracle does not support this syntax. However, it doesn't mean that it cannot be done.

一. Solution1

There is a table T with col1 and col2 and you want to add a new column col3 after col1.

1. Rename the column col2 to col3 as:
ALTER TABLE tablename RENAME COLUMN col2 to col3;

2. Add a new column to the table
alter table t1 add (col2 datatype);

3.Now finally interchange the data contained in the two column:
UPDATE EMPLOYEE SET col2 = col3 ,col3 = col2;

commit;

Note: Data types of the interchanged columns should match.

二.Solution2

SQL>create table test (ename varchar2(20),salary number);
Table created

SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER

SQL>rename test to test1;
Table renamed
SQL>create table test2 (id varchar2(20));
Table created
SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
Table created
........................................................................................
SQL>desc test;
Name Null? Type
----------------------------------------- -------- --------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/628922/viewspace-712580/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 准备搞定OCP
请登录后发表评论 登录
全部评论

注册时间:2009-05-08

  • 博文量
    107
  • 访问量
    380214