The syntax for implementing update Association update in SQL Server and Oracle is different. You can use inline view (embedded view)
In general, sqlserver is simpler. The test example is as follows:
Create Table tmp_a
(Cpcode varchar2 (10 ),
Sb_ym varchar2 (6 ),
Flag char (1)
);
Create Table tmp_ B
(Cpcode varchar2 (10 ),
Sb_ym varchar2 (6 ),
Flag char (1)
);
Insert into tmp_a (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Insert into tmp_a (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('123456', '123456', 'R ');
Insert into tmp_ B (cpcode, sb_ym, flag) values ('20140901', '20160901', 'E ');
Commit;
In sqlserver:
Update tmp_ B set flag = B. flang from tmp_a A, tmp_ B B
Where a. cpcode = B. cpcode and A. sb_ym = B. sb_ym;
In ORACLE:
Method 1: (low efficiency)
Update tmp_ B
Set flag = (select flag from tmp_a B
Where a. cpcode = B. cpcode and A. sb_ym = B. sb_ym)
Where exists
(Select * From tmp_a C
Where a. cpcode = C. cpcode and A. sb_ym = C. sb_ym );
Statistics
----------------------------------------------------------
8 recursive cballs
3 dB block gets
18 consistent gets
0 physical reads
0 redo size
Method 2: (high efficiency)
Alter table tmp_a add constraint p_tmp_a primary key (cpcode, sb_ym );
Update (select B. Flag flagb, A. Flag flaga
From tmp_a A, tmp_ B B
Where a. cpcode = B. cpcode
And a. sb_ym = B. sb_ym)
Set flagb = flaga;
Statistics
----------------------------------------------------------
0 recursive cballs
3 dB block gets
7 consistent gets
0 physical reads
0 redo size
Note: The primary key must be added to the data source table in method 2; otherwise, an error is returned.
ORA-01779: The columns corresponding to the non-key save table cannot be modified
You do not need to add a primary key to the modified table.