oracle 同时更新(update)多个字段多个值

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/bbqk9/article/details/5890892

--创建表A,B:

create table A (a1 varchar2(33),a2 varchar2(33),a3 varchar2(33));

create table B (b1 varchar2(33),b2 varchar2(33),b3 varchar2(33));

 

--插入数据 

insert into A values('1','aa','100');

insert into A values('2','bb','100');

insert into A values('3','cc','');

insert into A values('4','dd','200');

 

insert into B values('1','XX','10000');

insert into B values('2','YY','10000');

insert into B values('4','ZZ','20000');

insert into B values('5','KK','');

 

commit;

 

--更新前的表A,B:

 

                  

 

 

 

--对表A的a2,a3进行更新(带条件);

update  A

  set (A.a2,A.a3) =(select B.b2,b.b3

  from  B

  where B.b1= A.a1 and A.a3=100

  )

      where exists

 (select 'X' from B where B.b1=A.a1 and A.a3=100)  ;

 

commit;

 

--或者: 

update  A

  set (A.a2,A.a3 )=

  (select B.b2,b.b3

  from  B

  where B.b1= A.a1 and A.a3=100

  )

  where (A.a1) in (select
 B.b1 from  B

  where B.b1 = A.a1

  and A.a3 =100
  );

commit;

 

 

--更新后的表A:

 

展开阅读全文

没有更多推荐了,返回首页