Insert into with union all 和 nextval 对重复值不起作用 [英] Insert into with union all and nextval doesn't work with duplicate values

问题描述

我想使用 Oracle SQL 在一个 insert into 语句中的表中插入 2 行.

此代码有效:

insert into a_glw select tt.*, work_id_seq.nextval from(选择 11111, 'one text', 12345, 'new text', NULL,一些文字"、不错的文字"、测试"、文字"、很棒的文字"从双重联合所有选择 11111, '一个文本', 12345, '新文本', NULL,一些文字"、不错的文字"、测试"、文字"、很棒的文字"从双)tt;

当我将值 test 更改为 text 时，此代码会产生错误 00918.00000 - 列定义不明确":

insert into a_glw select tt.*, work_id_seq.nextval from(选择 11111, 'one text', 12345, 'new text', NULL,一些文字"、不错的文字"、文字"、文字"、很棒的文字"从双重联合所有选择 11111, '一个文本', 12345, '新文本', NULL,一些文字"、不错的文字"、测试"、文字"、很棒的文字"从双)tt;

在一个 select 语句中插入相同的值似乎是一个问题.我该如何解决这个问题?

解决方案

由于第二个示例中的值不同，您必须为列指定别名才能执行插入语句.

在第一个示例中，test 是列值，它假定 test 作为默认列名，因为您没有提供别名.

查看示例

I want to insert 2 lines in a table within one insert into statement with Oracle SQL.

This code works:

insert into a_glw select tt.*, work_id_seq.nextval from 
    (select 11111, 'one text', 12345, 'new text', NULL, 
    'some text', 'nice text', 'test', 'text', 'great text' 
    from dual 
union all 
    select 11111, 'one text', 12345, 'new text', NULL, 
    'some text', 'nice text', 'test', 'text', 'great text' 
    from dual) tt;

When I change the value test to text this code produces the error 00918. 00000 - "column ambiguously defined":

insert into a_glw select tt.*, work_id_seq.nextval from 
    (select 11111, 'one text', 12345, 'new text', NULL, 
    'some text', 'nice text', 'text', 'text', 'great text' 
    from dual 
union all 
    select 11111, 'one text', 12345, 'new text', NULL, 
    'some text', 'nice text', 'test', 'text', 'great text' 
    from dual) tt;

It seems to be a problem to insert identical values in one select statement. How can I fix this?

解决方案

As the values are different in the second example, you have to have an alias name for your columns in order to execute the insert statement.

In the first example, test is the column value and it assumes test as the default column name as you did not provide alias name.

See the example here

If you look at the enclosed screenshot, the second example is having TEXT columns repeated twice as the select statement is considering the column value as the column name and therefore you must provide alias names for the columns.

这篇关于Insert into with union all 和 nextval 对重复值不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！