UPDATE CLMS01.cvsobjcmminfo A SET
(A.GAGENM ,
A.PLIMORCUSID,
A.PLIMCUSNM ,
A.PLIMORTYP ,
A.GAGEBIG ,
A.GAGESUBTYP ,
A.GAGEFNS ,
A.PLESTS ,
A.GAGELOCPRO ,
A.GAGELOCCIT ,
A.GAGELOCBOR ,
A.GAGELOCSTR
) = (SELECT B.PLENM,
TO_CHAR(C.RELATIONID) , D.GUAIMPPSNNM,
D.PLIMORTYP,
B.PLEBIG,
B.PLESUBTYP,
B.PLEFNS,
B.PLESTS,
B.GAGESEATOMIT,
B.GAGESEATCITY,
B.GAGESEATSECT,
B.GAGESEATSTRT
FROM CCMSBAT.BLDITYP_GAGE_OBJ_INFO B,CLMS01.CUSPER C,CLMS01.CVSGAGEBASINFO D,CLMS01.cvsobjcmminfo A
WHERE A.GAGENO = B.GAGENO AND A.GAGESN = B.GAGESN AND D.GAGENO = B.GAGENO
AND C.CUSTOMERCODE = D.GUAIMPPSNCUSNO AND D.PLIMORTYP = '01'
AND B.RGNCDE = '40004');更新记录集的时候,我在plsql developer 中执行sql时报:single-row subquery returns more than one row,可是我写在存储过程中用命令执行时,又没报这个错,报invalid character,不知道怎么回事!高人指点一下,我查不出问题了,谢谢哈!
(A.GAGENM ,
A.PLIMORCUSID,
A.PLIMCUSNM ,
A.PLIMORTYP ,
A.GAGEBIG ,
A.GAGESUBTYP ,
A.GAGEFNS ,
A.PLESTS ,
A.GAGELOCPRO ,
A.GAGELOCCIT ,
A.GAGELOCBOR ,
A.GAGELOCSTR
) = (SELECT B.PLENM,
TO_CHAR(C.RELATIONID) , D.GUAIMPPSNNM,
D.PLIMORTYP,
B.PLEBIG,
B.PLESUBTYP,
B.PLEFNS,
B.PLESTS,
B.GAGESEATOMIT,
B.GAGESEATCITY,
B.GAGESEATSECT,
B.GAGESEATSTRT
FROM CCMSBAT.BLDITYP_GAGE_OBJ_INFO B,CLMS01.CUSPER C,CLMS01.CVSGAGEBASINFO D,CLMS01.cvsobjcmminfo A
WHERE A.GAGENO = B.GAGENO AND A.GAGESN = B.GAGESN AND D.GAGENO = B.GAGENO
AND C.CUSTOMERCODE = D.GUAIMPPSNCUSNO AND D.PLIMORTYP = '01'
AND B.RGNCDE = '40004');更新记录集的时候,我在plsql developer 中执行sql时报:single-row subquery returns more than one row,可是我写在存储过程中用命令执行时,又没报这个错,报invalid character,不知道怎么回事!高人指点一下,我查不出问题了,谢谢哈!
update 数据表
set 字段名1=(select 字段 or 运算 from 数据表 where 条件),字段2=(select 字段 or 运算 from 数据表 where 条件)......需要1个字段1个字段的写
create table t3 (aid varchar2(100));insert into t1 values('1');
insert into t3 values('1');
insert into t3 values('1');
update t1 set (aid)=(select aid from t3 where t1.aid=t3.aid)
注意t3两条同样的记录与t1进行关联,你测试一下看看是不是就是你的问题ORA-01427 单行子查询返回多于一行
update table
set (n个字段)
=(select n个字段 from table2)
where 条件
学习
我好像知道问题所在了:我没有在update时加限制条件,比如目标表原来有100条记录,我查出来的记录集是10条的话,那么必须通过where匹配出要更新的那十条才行,否则,他会update所有的,默认字段全为空,如果有非空限制就会出错,不知是否在理