英文:
DB2 ADD a new COLUMN to a table with DEFAULT value from an EXPRESSION using existing data in another column
问题
ALTER TABLE my_table ADD COLUMN new_column_name data_type DEFAULT expression;
在DB2中，是否可以使用现有列的数据作为新列的默认值？
ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT (man_days);
或者像这样使用默认值的表达式？
ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT ( CASE WHEN salary < 50000 THEN (man_days + 10) * 24 WHEN salary >= 50000 THEN (man_days) * 24 END);
英文:
ALTER TABLE my_table ADD COLUMN new_column_name data_type DEFAULT expression;
Is it possible to use an existing column data as default value for a new column in DB2?
ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT (man_days);
Or an expression like this for the default value?
ALTER TABLE employees ADD COLUMN work_hours INT DEFAULT ( CASE WHEN salary < 50000 THEN (man_days + 10) * 24
WHEN salary >= 50000 THEN (man_days) * 24 END);
答案1
得分: 2
目前可用的IBM Db2的版本，无论是在哪个平台上，都不能使用ALTER TABLE语句来指定基于表达式的默认值。
对于default-clause的语法，Db2平台（LUW、Z/OS、i）之间存在轻微的差异，但都限制选项基于空值、特殊寄存器或常量。
您可以通过DDL和随后的SQL语句的组合来实现您的需求。
英文:
With currently shipping versions of IBM Db2, regardless of the platform, you cannot use an ALTER TABLE statement to specify an expression-based default value.
There are minor differences between the Db2-platforms (LUW, Z/OS, i) for the syntax of the default-clause, but all limit the options to be based on null or special registers or constants.
You can achieve what you need by a combination of DDL and subsequent SQL statements.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论