db2 9.5 ,将字符串类型字段更新为date类型失败

2016-9-26 19:02:21 @hury
db2 9.5 中，将字符串类型字段更新为date类型失败；
解决办法：将数据备份后，重建该列；

-- 1、如下脚本执行失败
-- ALTER TABLE table_business ALTER COLUMN date_col SET DATA TYPE DATE;

-- 2、建立备份表
CREATE TABLE tmp_20160926 LIKE table_business;

SELECT * FROM tmp_20160926;

-- 3、将数据写入临时表
INSERT INTO tmp_20160926(ID,date_col)
SELECT id, date_col 
FROM table_business 
WHERE date_col IS NOT NULL 

-- 4、重建列
--ALTER TABLE table_business DROP COLUMN date_col;
--ALTER TABLE table_business ADD COLUMN date_col DATE 

-- 5、重组表
db2 reorg table "DB2INST1"."table_business"
db2 runstats ON table  "DB2INST1"."table_business"

-- 6、编写查询算法
SELECT A.id, b.date_col
FROM table_business A, tmp_20160926 B
WHERE A.ID = B.ID 

-- 7、更新数据
UPDATE table_business a SET date_col = (
SELECT b.date_col
FROM tmp_20160926 B
WHERE A.ID = B.ID 
)
WHERE EXISTS(
SELECT b.date_col
FROM tmp_20160926 B
WHERE A.ID = B.ID 
)

搞定。