Update TEMP_0001_154 a
set a.TEMP2 = (select TEMP1 from TEMP2_487 A
where A.FDI_NO='V_CXC_MAIN_PROD'
and A.DIMENSION2='I001'
and (A.ACCT_DATE,A.COMP_CODE,A.DIMENSION1,A.DIMENSION3)
in ( select ACCT_DATE,COMP_CODE,DIMENSION1,DIMENSION3 from TEMP_0001_154 ))
where (a.fdi_no,a.ACCT_DATE,a.COMP_CODE,a.DIMENSION1,a.dimension2,a.DIMENSION3)
in ( select fdi_no,ACCT_DATE,COMP_CODE,DIMENSION1,dimension2,DIMENSION3 from TEMP_0001_154 where FDI_NO='V_CXC_MAIN_PROD' and DIMENSION2='I001')我是目的是想从TEMP2_487 中取得TEMP1列将TEMP_0001_154 的中TEMP2列更新,但是总是返回ORA-01427:单行子查询多于一行,请问如何才能更新。在里边的条件我应该怎么写?
set a.TEMP2 = (select TEMP1 from TEMP2_487 A
where A.FDI_NO='V_CXC_MAIN_PROD'
and A.DIMENSION2='I001'
and (A.ACCT_DATE,A.COMP_CODE,A.DIMENSION1,A.DIMENSION3)
in ( select ACCT_DATE,COMP_CODE,DIMENSION1,DIMENSION3 from TEMP_0001_154 ))
where (a.fdi_no,a.ACCT_DATE,a.COMP_CODE,a.DIMENSION1,a.dimension2,a.DIMENSION3)
in ( select fdi_no,ACCT_DATE,COMP_CODE,DIMENSION1,dimension2,DIMENSION3 from TEMP_0001_154 where FDI_NO='V_CXC_MAIN_PROD' and DIMENSION2='I001')我是目的是想从TEMP2_487 中取得TEMP1列将TEMP_0001_154 的中TEMP2列更新,但是总是返回ORA-01427:单行子查询多于一行,请问如何才能更新。在里边的条件我应该怎么写?
set a.TEMP2 = (select TEMP1 from TEMP2_487 A
where A.FDI_NO='V_CXC_MAIN_PROD'
and A.DIMENSION2='I001'
and (A.ACCT_DATE,A.COMP_CODE,A.DIMENSION1,A.DIMENSION3)
= ( select ACCT_DATE,COMP_CODE,DIMENSION1,DIMENSION3 from TEMP_0001_154 ))
where (a.fdi_no,a.ACCT_DATE,a.COMP_CODE,a.DIMENSION1,a.dimension2,a.DIMENSION3)
in ( select fdi_no,ACCT_DATE,COMP_CODE,DIMENSION1,dimension2,DIMENSION3 from TEMP_0001_154 where FDI_NO='V_CXC_MAIN_PROD' and DIMENSION2='I001')
试下
= ( select ACCT_DATE,COMP_CODE,DIMENSION1,DIMENSION3 from TEMP_0001_13 )还是不行。提示=( select ACCT_DATE,COMP_CODE,DIMENSION1,DIMENSION3 from TEMP_0001_13 )01427:单行子查询多于一行
http://community.csdn.net/Expert/topic/5346/5346700.xml?temp=.9562342
这个是我写的同步更新,里面也是包含多个条件的
另外,你的查询条件有问题
你必须要保证查询出来的结果是一行的
请问select 1这句是什么意思?
where exists(select 1 from v_ehr_eit_users b where b.user_name=a.user_name)
create table tmp_a
(cpcode varchar(10),
sb_ym varchar(6),
flag char(1)
);
create table tmp_b
(cpcode varchar(10),
sb_ym varchar(6),
flag char(1)
);
插入测试数据
insert into tmp_a(cpcode,sb_ym,flag)values('3201910001','200406','e');
insert into tmp_a(cpcode,sb_ym,flag)values('3201910002','200406','e');
insert into tmp_b(cpcode,sb_ym,flag)values('3201910001','200406','r');
insert into tmp_b(cpcode,sb_ym,flag)values('3201910002','200406','r');
insert into tmp_b(cpcode,sb_ym,flag)values('3201910003','200406','r');
insert into tmp_b(cpcode,sb_ym,flag)values('3201910004','200406','e');用tmp_b表中记录批量更新tmp_a表中记录
update tmp_a set flag = b.flag,sb_ym=b.sb_ym from tmp_a a,tmp_b b
where a.cpcode =b.cpcode
update tmp_a set (flag,sb_ym)=(Select b.flag,b.sb_ym from tmp_a a,tmp_b b
where a.cpcode =b.cpcode )-MS SqlServer-中语法 update tmp_a set flag = b.flag,sb_ym=b.sb_ym from tmp_a a,tmp_b b
where a.cpcode =b.cpcode