Oracle,从子查询中插入多行,且该行多于一行 [英] Oracle, insert multirows from subquery with more than one row
本文介绍了Oracle,从子查询中插入多行,且该行多于一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将表中的某些字段复制到其他字段,我想通过将insert与这样的子查询一起使用来完成操作:
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"
知道我该怎么做吗?
谢谢
推荐答案
认为您必须在两者之间进行选择
Think you got to choose between
insert into table (a, b, c) VALUES(1, 2, 3)
和
insert into table (a, b, c)
(SELECT x, y, z from table2)
您只能将VALUES和SELECT混合使用(正如一个失常的马所指出的那样) 当您的选择查询仅返回一列和一行!
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 !
顺便说一句,使用JOIN ...联接表(使用WHERE子句联接表是一种坏习惯):
By the way, use JOIN... to Join your tables (use of the WHERE clauses to join tables is rather a bad habit) :
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)
);
这篇关于Oracle,从子查询中插入多行,且该行多于一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文