以下语句执行后报 ORA-01427: 单行子查询返回多个行 这个错误，请问是怎么解决呢？多谢
update IRPT_DEPARTMENTS a
set a.FZJG=
(
select wt1_1.C48
from WSXXZB.WT1_1NB_B1 wt1_1
where wt1_1.Userid_=a.id and wt1_1.bbq_='2007----'
union all
select wt1_2.C27
from WSXXZB.WT1_2NB_B1 wt1_2
where wt1_2.Userid_=a.id and wt1_2.bbq_='2007----'
union all
select wt1_6.C23
from WSXXZB.WT1_6NB_B1 wt1_6
where wt1_6.Userid_=a.id and wt1_6.bbq_='2007----'
union all
select wt1_7.C28
from WSXXZB.WT1_7NB_B1 wt1_7
where wt1_7.Userid_=a.id and wt1_7.bbq_='2007----'
union all
select wt1_8.C22
from WSXXZB.WT1_8NB_B1 wt1_8
where wt1_8.Userid_=a.id and wt1_8.bbq_='2007----'
)
where exists
(
select 1
from WSXXZB.WT1_1NB_B1 wt1_1
where wt1_1.Userid_=a.id and wt1_1.bbq_='2007----'
union all
select 1
from WSXXZB.WT1_2NB_B1 wt1_2
where wt1_2.Userid_=a.id and wt1_2.bbq_='2007----'
union all
select 1
from WSXXZB.WT1_6NB_B1 wt1_6
where wt1_6.Userid_=a.id and wt1_6.bbq_='2007----'
union all
select 1
from WSXXZB.WT1_7NB_B1 wt1_7
where wt1_7.Userid_=a.id and wt1_7.bbq_='2007----'
union all
select 1
from WSXXZB.WT1_8NB_B1 wt1_8
where wt1_8.Userid_=a.id and wt1_8.bbq_='2007----'
);
------解决方案--------------------
你返回了一个结果集。
set后面跟的只能是具体的数据。
增加查询条件。
具体解决方法由于不知道你想干什么。回答不上。
------解决方案--------------------
set a.FZJG=
(
select wt1_1.C48
from WSXXZB.WT1_1NB_B1 wt1_1
where wt1_1.Userid_=a.id and wt1_1.bbq_='2007----'
union all
select wt1_2.C27
from WSXXZB.WT1_2NB_B1 wt1_2
where wt1_2.Userid_=a.id and wt1_2.bbq_='2007----'
union all
select wt1_6.C23
from WSXXZB.WT1_6NB_B1 wt1_6
where wt1_6.Userid_=a.id and wt1_6.bbq_='2007----'
union all
select wt1_7.C28
from WSXXZB.WT1_7NB_B1 wt1_7
where wt1_7.Userid_=a.id and wt1_7.bbq_='2007----'
union all
select wt1_8.C22
from WSXXZB.WT1_8NB_B1 wt1_8
where wt1_8.Userid_=a.id and wt1_8.bbq_='2007----'
)
这段里面的select出现多行数据了
每行更新字段只能对应一个更新内容啊
------解决方案--------------------
像 二楼说的，把条件追加上，set 只能一对一。你的SQL好比是：
update tablename1 a set a.id=(select b.id from tablename2 b)
------解决方案--------------------
- SQL code
试试:UPDATE IRPT_DEPARTMENTS A SET A.FZJG = (SELECT WT1_1.C48 FROM WSXXZB.WT1_1NB_B1 WT1_1 WHERE WT1_1.USERID_ = A.ID AND WT1_1.BBQ_ = '2007----' UNION ALL SELECT WT1_2.C27 FROM WSXXZB.WT1_2NB_B1 WT1_2 WHERE WT1_2.USERID_ = A.ID AND WT1_2.BBQ_ = '2007----' UNION ALL SELECT WT1_6.C23 FROM WSXXZB.WT1_6NB_B1 WT1_6 WHERE WT1_6.USERID_ = A.ID AND WT1_6.BBQ_ = '2007----' UNION ALL SELECT WT1_7.C28 FROM WSXXZB.WT1_7NB_B1 WT1_7 WHERE WT1_7.USERID_ = A.ID AND WT1_7.BBQ_ = '2007----' UNION ALL SELECT WT1_8.C22 FROM WSXXZB.WT1_8NB_B1 WT1_8 WHERE WT1_8.USERID_ = A.ID AND WT1_8.BBQ_ = '2007----' WHERE ROWNUM <= 1) WHERE EXISTS (SELECT 1 FROM WSXXZB.WT1_1NB_B1 WT1_1 WHERE WT1_1.USERID_ = A.ID AND WT1_1.BBQ_ = '2007----' UNION ALL SELECT 1 FROM WSXXZB.WT1_2NB_B1 WT1_2 WHERE WT1_2.USERID_ = A.ID AND WT1_2.BBQ_ = '2007----' UNION ALL SELECT 1 FROM WSXXZB.WT1_6NB_B1 WT1_6 WHERE WT1_6.USERID_ = A.ID AND WT1_6.BBQ_ = '2007----' UNION ALL SELECT 1 FROM WSXXZB.WT1_7NB_B1 WT1_7 WHERE WT1_7.USERID_ = A.ID AND WT1_7.BBQ_ = '2007----' UNION ALL SELECT 1 FROM WSXXZB.WT1_8NB_B1 WT1_8 WHERE WT1_8.USERID_ = A.ID AND WT1_8.BBQ_ = '2007----');