帮忙看看我这个语句为什么不行,谢谢。update A a set a.lsrq=(select b.lsrq from B b where a.id=b.id and b.jwh='xxx村' and b.djrq>=to_date('2006-01-01','yyyy-mm-dd') and b.djrq<to_date('2006-06-28','yyyy-mm-dd') and b.lx='201)' where a.id in (select c.id from B c where c.jwh='xxx村' and c.djrq>=to_date('2006-01-01','yyyy-mm-dd') and c.djrq<to_date('2006-06-28','yyyy-mm-dd') and c.lx='201')
错误提示:single-row subquery returns more than one row
UPDATE t_A SET Djrq= ( SELECT djrq FROM t_B WHERE t_A.ID = T_B.ID WHERE ROWNUM = 1 ) WHERE t_A.ID IN ( SELECT ID FROM t_B WHERE jwh='XX村' )
根均条件 a.id=b.id and b.jwh='xxx村' and b.djrq>=to_date('2006-01-01','yyyy-mm-dd') and b.djrq<to_date('2006-06-28','yyyy-mm-dd') and b.lx='201' 确定的是a表的一条记录对应b表的多条记录,所以出现single-row subquery returns more than one row错误楼主需要确定一下更新的逻辑是否有问题
UPDATE A SET DJRQ=(SELECT DJRQ FROM B WHERE B.ID=A.ID AND B.JWH='xx村' and rownum=1) where a.id in (SELECT ID from B)是不是用ID来关联的啊?如果是就用上面的sql来更新!
JOIN B ON A.ID=B.ID AND B.JWH='xx村'
(
SELECT djrq FROM t_B WHERE t_A.ID = T_B.ID
WHERE ROWNUM = 1
)
WHERE t_A.ID IN
(
SELECT ID FROM t_B WHERE jwh='XX村'
)
a.id=b.id and b.jwh='xxx村' and b.djrq>=to_date('2006-01-01','yyyy-mm-dd') and b.djrq<to_date('2006-06-28','yyyy-mm-dd') and b.lx='201'
确定的是a表的一条记录对应b表的多条记录,所以出现single-row subquery returns more than one row错误楼主需要确定一下更新的逻辑是否有问题