以下语句执行后报 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----'
);
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----'
);
解决方案 »
- 急等Oracle9i数据库(dmp文件)恢复方法!
- 经常遇见的问题?
- Oracle类型转换,纠结的问题(只有23分,悲催了。。。 )
- oracle连接错误
- 存储过程中执行创建同义词的语句出错
- 求助一SQL语句(如何UPDATE)
- 请问在奔四机上安装Oracle8.17的兼容性问题(在线等)
- 请教各位一个在oracle中实现直接提交的方法
- MTS配置问题?向高人求教!
- 爲什麽IMP用戶資料時,佔用的TABLESPACES會變大很多?
- 为oracle创建表后,在查询表时都要必须这:select * from 用户名.表名;这是为什么呢?
- Oracle 返回游标 PLS-00103: 出现符号 "CREATE"
set后面跟的只能是具体的数据。增加查询条件。
具体解决方法由于不知道你想干什么。回答不上。
(
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出现多行数据了
每行更新字段只能对应一个更新内容啊
试试:
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----');