Question

ORA-01427: 单行子查询返回多于一个行,请教高手：怎么改？

update itemactivitydetail id
set id.lngorderdetailid = (select lngactivitydetailid27
from (select id1.lngactivityid,
id1.lngactivitydetailid,
id1.lngitemid
from itemactivity i1,
itemactivitydetail id1
where i1.lngactivityid =
id1.lngactivityid
and i1.lngactivitytypeid = 28
and id1.lngorderdetailid = 0) a28,
(select id2.lngitemid,
i2.lngorderactivityid,
id2.lngactivitydetailid as lngactivitydetailid27
from itemactivity i2,
itemactivitydetail id2
where i2.lngactivityid =
id2.lngactivityid
and i2.lngactivitytypeid = 27) a27
where a28.lngitemid = a27.lngitemid
and a28.lngactivityid =
a27.lngorderactivityid
and a28.lngactivitydetailid =
id.lngactivitydetailid)
where exists
(select 1
from (select id1.lngactivityid,
id1.lngactivitydetailid,
id1.lngitemid
from itemactivity i1, itemactivitydetail id1
where i1.lngactivityid = id1.lngactivityid
and i1.lngactivitytypeid = 28
and id1.lngorderdetailid = 0) a28,
(select id2.lngitemid,
i2.lngorderactivityid,
id2.lngactivitydetailid as lngactivitydetailid27
from itemactivity i2, itemactivitydetail id2
where i2.lngactivityid = id2.lngactivityid
and i2.lngactivitytypeid = 27) a27
where a28.lngitemid = a27.lngitemid
and a28.lngactivityid = a27.lngorderactivityid
and a28.lngactivitydetailid = id.lngactivitydetailid)

Answer 1

在update的时候，子查询返回是多条，你首先必须要确定怎么从这多条里面选择一条出来，比如你可以选最大的select max(lngactivitydetailid27) 最小的select min(lngactivitydetailid27)，当然也可以选第一条出来，改完的句子类似于：
update itemactivitydetail id
set id.lngorderdetailid = (select max(lngactivitydetailid27)
from (select id1.lngactivityid,
id1.lngactivitydetailid,
id1.lngitemid
from itemactivity i1,
itemactivitydetail id1
where i1.lngactivityid =
id1.lngactivityid
and i1.lngactivitytypeid = 28
and id1.lngorderdetailid = 0) a28,
(select id2.lngitemid,
i2.lngorderactivityid,
id2.lngactivitydetailid as lngactivitydetailid27
from itemactivity i2,
itemactivitydetail id2
where i2.lngactivityid =
id2.lngactivityid
and i2.lngactivitytypeid = 27) a27
where a28.lngitemid = a27.lngitemid
and a28.lngactivityid =
a27.lngorderactivityid
and a28.lngactivitydetailid =
id.lngactivitydetailid
and rownum <=1)
where exists
(select 1
from (select id1.lngactivityid,
id1.lngactivitydetailid,
id1.lngitemid
from itemactivity i1, itemactivitydetail id1
where i1.lngactivityid = id1.lngactivityid
and i1.lngactivitytypeid = 28
and id1.lngorderdetailid = 0) a28,
(select id2.lngitemid,
i2.lngorderactivityid,
id2.lngactivitydetailid as lngactivitydetailid27
from itemactivity i2, itemactivitydetail id2
where i2.lngactivityid = id2.lngactivityid
and i2.lngactivitytypeid = 27) a27
where a28.lngitemid = a27.lngitemid
and a28.lngactivityid = a27.lngorderactivityid
and a28.lngactivitydetailid = id.lngactivitydetailid)