报ORA-01427错

显示全部楼层 · 2007-9-26 18:42:10

UPDATE T_consign
SET (gathering,department) =(
select c.payee,c.agent
from T_consign inner join
(
selectID,A.container_rn, max(B.payee) as payee, max(B.agent) as agent
FROM T_consign A INNER JOIN
(SELECT * FROM containerFee WHERE type = '应收' AND name='水运费') B
ON A.container_rn = B.consignContainer_rn
WHERE(ID = 58490994)
group by ID,A.container_rn
) c on T_consign.id =c.id and T_consign.container_rn =c.container_rn
)
WHERE(ID = 58490994)
;

我不明白我做了了内联试图，然后更新T_consign，关键字也加了这么还有SINGLE-ROW SUBQUERY RETURNS MORE THAN ONE ROW

千问 · 2007-9-26 18:42:10

ID 是 unique吗？还是ID和container_rn 才能保证unique？

千问 · 2007-9-26 18:42:10

是ID和container_rn 才能保证unique

select c.payee,c.agent
from T_consign inner join
(
select ID,A.container_rn, max(B.payee) as payee, max(B.agent) as agent
FROM T_consign A INNER JOIN
(SELECT * FROM containerFee WHERE type = '应收' AND name='水运费') B
ON A.container_rn = B.consignContainer_rn
WHERE (ID = 58490994)
group by ID,A.container_rn
) c on T_consign.id =c.id and T_consign.container_rn =c.container_rn
)
出来17条记录，
T_consign的WHERE条件共有84条

千问 · 2007-9-26 18:42:10

报ORA-01427错这个错误主要是指什么，那里返回多条记录了？？？？

千问 · 2007-9-26 18:42:10

最初由 perry_shi 发布
[B]UPDATE T_consign
SET (gathering,department) =(
select c.payee,c.agent
from T_consign inner join
(
selectID,A.container_rn, max(B.payee) as payee, max(B.agent) as agent
FROM T_consign A INNER JOIN
(SELECT * FROM containerFee WHERE type = '应收' AND name='水运费') B
ON A.container_rn = B.consignContainer_rn
WHERE(ID = 58490994)
group by ID,A.container_rn
) c on T_consign.id =c.id and T_consign.container_rn =c.container_rn
)
WHERE(ID = 58490994)
;

我不明白我做了了内联试图，然后更新T_consign，关键字也加了这么还有SINGLE-ROW SUBQUERY RETURNS MORE THAN ONE ROW [/B]

"from T_consign inner join
(
"
这里的T_consign 是多余的
如果要更新多条记录的：
update a set a.b = (select b.b from b where a.a = b.a) where ....即可！

参考http://www.itpub.net/608473.html

千问 · 2007-9-26 18:42:10

谢谢，对了，谢谢了