ora-01427批量更新表的时候提示单行子查询返回多个行

这是刚开始的更新语句:根据AMCARD表的ACCTCOMPID和ACCTDEPID字段关联LSBMZD表的LSBMZD_DWBH和LSBMZD_BMBH,得到相对于的LSBMZD_ID,然后根据这个LSBMZD_ID列匹配HRORGINFO表的MAPPINGORG字段,最终得到HRORGINFO表的NM字段,将AMCARD的USEDEPTID字段批量更新为NM字段

UPDATE AMCARD SET USEDEPTID=(
SELECT NM FROM HRORGINFO WHERE MAPPINGORG=(
SELECT LSBMZD_ID FROM LSBMZD WHERE LSBMZD_DWBH=ACCTCOMPID AND LSBMZD_BMBH=ACCTDEPTID
)
)

执行之后得到错误提示:


然后根据更新语句写出查询语句

SELECT AMID,NM,ACCTCOMPID,ACCTDEPTID,LSBMZD_ID,MAPPINGORG
 FROM AMCARD,LSBMZD,HRORGINFO 
 WHERE USEDEPTID IS NULL
 AND LSBMZD_DWBH=ACCTCOMPID
 AND LSBMZD_BMBH=ACCTDEPTID
 AND LSBMZD_ID=MAPPINGORG

得出查询结果:


很容易看出NM字段是大量重复值组成的,所以才会提示单行子查询返回多个行。

我的解决办法是根据唯一的ID列形成一一对应再执行更新,保证每一行只有一条记录就可以了。

要更新的AMCARD表中AMID是唯一的,因此我把AMID加入到查询结果中作为临时表t,再根据AMID=t.AMID就可以实现单行更新了,执行语句如下:

UPDATE AMCARD SET USEDEPTID=(
 SELECT NM FROM(SELECT AMID AS A,NM
 FROM AMCARD,LSBMZD,HRORGINFO 
 WHERE USEDEPTID IS NULL
 AND LSBMZD_DWBH=ACCTCOMPID
 AND LSBMZD_BMBH=ACCTDEPTID
 AND LSBMZD_ID=MAPPINGORG)T
 WHERE AMID=T.A
 )
 WHERE USEDEPTID IS NULL
这样就可以执行了。
with order_base as --获取订单基础情况 ( select ou.order_key order_key, ou.order_quantity_i, ood.dispatch_time_t, ou.part_number_s, 'I032ZZ01' pline_name_s from order_uv ou left join at_as_om_orderdispatchstatus ood on ou.order_key = ood.order_54 union all select mo.atr_key order_key, mo.order_quantity_i, mo.dispatch_time_t, mo.part_number_s, to_char(mo.pline_name_s) pline_name_s from at_as_mm_order_plan mo ) , plan_quantity_base as --根据订单派发时间确定订单派发的班次 ( select to_char(ob.dispatch_time_t,'yyyymmdd') work_day, dispatch_time_t, ( select su.description shift_name from at_as_sm_calendar sc, shift_uv su where su.shift_key = sc.shift_198 and category_s = 'MFG' and target_s = ob.pline_name_s and target_type_s = 'Production Line' and ob.dispatch_time_t >= sc.start_time_t and ob.dispatch_time_t <= sc.end_time_t and to_char(ob.dispatch_time_t,'yyyymmdd') = to_char(sc.start_time_t,'yyyymmdd') ) shift_name, ob.pline_name_s, ob.order_quantity_i from order_base ob ) --select * from plan_quantity_base; --Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S --From Plan_Quantity_Base Pqb --group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S; --, --plan_quantity as ( --根据订单派发时间计算生产线的班次计划产量 select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity From Plan_Quantity_Base Pqb --Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line) --and pqb.shift_name like decode(:shift,'ALL','%%',:shift) group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S --) 问题描述: sql运到 plan_quantity 时报错:单查询返回多个 1、单独对每个语句块写sql语句查询,排查下来发现问题发生地方在 plan_quantity 上; 2、运语句(Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S From Plan_Quantity_Base Pqb)和(select * from plan_quantity_base)都没问题; 3、运语句 (Select To_Char(Pqb.Dispatch_Time_T,'yyyymmdd') Work_Day, Pqb.Shift_Name, Pqb.Pline_Name_S From Plan_Quantity_Base Pqb group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S) 和 ( select to_char(Pqb.dispatch_time_t,'yyyymmdd') work_day, pqb.shift_name, pqb.pline_name_s, sum(pqb.order_quantity_i) plan_quantity From Plan_Quantity_Base Pqb --Where Pqb.Pline_Name_S Like Decode(roduction_Line,'ALL','%%',Null,'%%',roduction_Line) --and pqb.shift_name like decode(:shift,'ALL','%%',:shift) group By to_char(Pqb.dispatch_time_t,'yyyymmdd'), Pqb.Shift_Name, Pqb.Pline_Name_S) 报错:单查询返回多个; 问题疑问: 1、在 plan_quantity 语句块中,已经group by 了,并且查询的列中并没有查询,为什么会发生这种错误? 请大家帮忙分析下,实在没有分析出来。 ![图片说明](https://img-ask.csdn.net/upload/201801/06/1515214129_18167.png) ![图片说明](https://img-ask.csdn.net/upload/201801/06/1515214146_738834.png)
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页