alter column data type保留存储的数据 [英] alter column data type preserving stored data

问题描述

大家好......

我有一个oracle 10g db，我想在一个表中修改一个列数据类型，这个列不是空的，它包含一些数据，我被建议创建一个新列，将旧数据移动到它，修改旧列数据类型，然后将数据返回到此列...这是最好的解决方案???如果是这样......如何实施呢???什么是脚本（我想将数据类型从nvarchar2更改为varchar2）



提前感谢...

解决方案

无需复制数据两次。看看这个小脚本：

  ALTER   TABLE  mytable  ADD  new_column VARCHAR2（ 64 ）;   -   根据您需要的尺寸交换64  
  UPDATE  mytable  SET  new_column = old_column; 
  ALTER   TABLE  mytable  DROP   COLUMN  old_column; 
  ALTER   TABLE  mytable RENAME  COLUMN  new_column  to  old_column; 

此处没有完成错误检查。我在这里假设从nvarchar到varchar的隐式转换可以毫无问题地进行。

hello everybody ...
I have an oracle 10g db ,I want to modify a column datatype in a table , this column is not empty , it contains some data , I was advised to create a new column , move the old data to it , modify the old column data type , then return the data back to this column ... Is this the best solution ??? If so ... how to implement it ??? what is the script (I want to change the data type from nvarchar2 to varchar2)

thanks in advance...

解决方案

No need to copy the data twice. Have a look at this tiny script:

ALTER TABLE mytable ADD new_column VARCHAR2(64); --exchange 64 for the size you need
UPDATE mytable SET new_column = old_column;
ALTER TABLE mytable DROP COLUMN old_column;
ALTER TABLE mytable RENAME COLUMN new_column to old_column;

There is no error checking done here. I''m assuming here that the implicit conversion from nvarchar to varchar is doable without problems.

这篇关于alter column data type保留存储的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！