oracle update set 多个字段,Oracle-update同时更新多个字段多个值

--创建表A,B：

create table CUX_TEST_TABLE_A (a1 varchar2(240),a2 varchar2(240),a3 varchar2(240));

create table CUX_TEST_TABLE_B (b1 varchar2(240),b2 varchar2(240),b3 varchar2(240));

--插入数据

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

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

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

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

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

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

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

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

commit;

--查询表CUX_TEST_TABLE_A

----查询表CUX_TEST_TABLE_B

--做同时更新多个字段的update语句

UPDATE cux_test_table_a a

SET    (a.a2

,a.a3) =

(SELECT b.b2

,b.b3

FROM   cux_test_table_b b

WHERE  b.b1 = a.a1

AND    a.a3 = 100)

WHERE  EXISTS (SELECT 'X'

FROM   cux_test_table_b b

WHERE  b.b1 = a.a1   AND    a.a3 = 100);

--再次查询表CUX_TEST_TABLE_A