Oracle - SQL - 使用子选择插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13838951/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - SQL - insert into with sub select
提问by Dr.Avalanche
I'm getting ORA-00947: not enough valuesfrom the query below:
我ORA-00947: not enough values从下面的查询中得到：
insert into tableb
(colA, colb, colc, cold)
select
(select max(rec_no)+1 from tableb)
F2,
F3,
F4
from tablea;
Can someone point me to the correct way to include a sub query for an inser into/select statement?
有人可以指出我为插入/选择语句包含子查询的正确方法吗？
Thanks
谢谢
回答by Thilo
You are just missing a comma. As it is, Oracle thinks F2is the name of your sub-select.
你只是缺少一个逗号。实际上，Oracle 认为F2是您的子选择的名称。
insert into tableb
(colA, colb, colc, cold)
select
(select max(rec_no)+1 from tableb) , -- comma here
F2,
F3,
F4
from tablea;
回答by a_horse_with_no_name
The only reliable, fast and scalable way to generate unique IDs is using sequences.
生成唯一 ID 的唯一可靠、快速和可扩展的方法是使用序列。
The reason why the max()"solution" won't work, is a transaction will not see uncommitted changes from another transaction. So two concurrent transactions can wind up using the same value for max()which in turn will generate duplicate id values.
max()“解决方案”不起作用的原因是一个事务不会看到来自另一个事务的未提交的更改。因此，两个并发事务最终会使用相同的值，max()而这些值又会生成重复的 id 值。
To create the values from a sequence in your case, you obviously need to first create a sequence:
要从您的案例中的序列创建值，您显然需要首先创建一个序列：
create sequence seq_b;
Then use that sequence in your select statement:
然后在您的选择语句中使用该序列：
insert into tableb
(colA, colb, colc, cold)
select seq_b.nextval,
F2,
F3,
F4
from tablea;
