我试图从另一个表插入数据到表,表只有一个共同的列。问题是,TABLE1有不接受空值的列,所以我不能将它们留空,我不能从TABLE2获取它们。
我有TABLE1:
id,col_1(not null),col_2(not null),col_3(not null)
和TABLE2:
id,col_a,col_b,col_c
所以我怎么能插入id从TABLE2到TABLE1和填充col_1-3硬编码字符串像“data1”,“data2”,“data3”?
INSERT INTO TABLE1 (id) SELECT id FROM TABLE2 WHERE col_a = "something";
将导致错误:列“col_1”中的空值违反了非空约束
只需在SELECT:
INSERT INTO TABLE1 (id,col_1,col_2,col_3) SELECT id,'data1','data2','data3' FROM TABLE2 WHERE col_a = 'something';
选择列表可以包含any value expression:
But the expressions in the select list do not have to reference any columns in the table expression of the FROM clause; they can be constant arithmetic expressions,for instance.
字符串字面量肯定是一个值表达式。