Oracle，从多行的子查询中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12740749/
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, insert multirows from subquery with more than one row
提问by Angelo Gabriel Escudero Vía
Im trying to copy some field from a table to other, I want to do iy by using insert with a subquery like this:
我试图将表中的某些字段复制到其他字段，我想通过使用带有子查询的插入来做到这一点：
insert into sed_reporte_generico
(srg_usuario,
srg_nombres,
srg_ape_paterno,
srg_ape_materno,
srg_objetivo,
srg_peso_ob,
srg_calf_ob)
values
(
(select us.su_st_usuario, us.su_st_nombres, us.su_st_ap_paterno, us.su_st_ap_materno, ob.soc_st_descripcion, ob.soc_nr_peso,ob.soc_nr_calificacion
from sed_objetivo ob, sed_usuarios us, sed_evaluacion ev
where ob.se_evaluacion_pk = ev.se_evaluacion_pk and ev.su_colaborador_fk = us.su_usuarios_pk)
);
but I got this error:
但我收到了这个错误：
01427. 00000 - "single-row subquery returns more than one row"
any idea how should I do this?
知道我该怎么做吗？
Thanks,
谢谢，
回答by Rapha?l Althaus
Think you got to choose between
认为你必须在两者之间做出选择
insert into table (a, b, c) VALUES(1, 2, 3)
and
和
insert into table (a, b, c)
(SELECT x, y, z from table2)
You can have VALUES and SELECT mixed only (as pointed by an anomyous horse) when your select query returns only one column and one row !
当您的选择查询仅返回一列和一行时，您只能将 VALUES 和 SELECT 混合（如异常马指出的那样）！
By the way, use JOIN... to Join your tables (use of the WHERE clauses to join tables is rather a bad habit) :
顺便说一下，使用 JOIN... 来连接你的表（使用 WHERE 子句来连接表是一个坏习惯）：
INSERT INTO sed_reporte_generico
(srg_usuario,
srg_nombres,
srg_ape_paterno,
srg_ape_materno,
srg_objetivo,
srg_peso_ob,
srg_calf_ob)
(select us.su_st_usuario, us.su_st_nombres, us.su_st_ap_paterno, us.su_st_ap_materno, ob.soc_st_descripcion, ob.soc_nr_peso,ob.soc_nr_calificacion
FROM sed_objetivo ob
JOIN sed_evaluacion ev ON ob.se_evaluacion_pk = ev.se_evaluacion_pk
JOIN sed_usuarios us on ev.su_colaborador_fk = us.su_usuarios_pk)
);
