Four SQL statements associated with two Oracle tables to update table information

In the Oracle database, two tables, T_1 and T_2, must be modified in batches Based on the T_2 table information. The two tables are associated by ID.
1. create two tables without the primary key create table T_1
(
Id number (2 ),
YEAR VARCHAR2 (20 ),
MONTH VARCHAR2 (10)
);
Create table T_2
(
Id number (2 ),
YEAR VARCHAR2 (20 ),
MONTH VARCHAR2 (10)
 
);
2. insert into T_1 (ID, YEAR, MONTH) values (1, '20140901', '1') into Table T_1 and table T_2 ');
Insert into T_1 (ID, YEAR, MONTH) values (2, '20140901', '2 ');
Insert into T_1 (ID, YEAR, MONTH) values (3, '2013', '3 ');
Commit;

Insert into T_2 (ID, YEAR, MONTH) values (1, '20140901', '11 ');
Insert into T_2 (ID, YEAR, MONTH) values (2, '20140901', '12 ');
Commit;

3. delete table data delete from T_1;
Delete from t_2;
Commit;

4. to update the data of table t_1 with the data of table t_2, the premise is that the IDs of the two tables

Method 1:
Update t_1 a set (a. year, a. month) = (select B. year, B. month from T_2 B where B. id = a. id );
Commit;
Result:
1 2010 11
2 2010 12
3
The year and month values of t_1.id = 3 are set to null in the execution result, because this statement is used to change the records of table t_1, if no record exists in Table T_2, the record in Table T_1 is left blank;
Solution: if you do not want to get such a result, you need to add a where condition.
Update t_1 a set (a. year, a. month) = (select B. year, B. month from T_2 B where B. id = a. id)
Where a. id = (select c. id from year4 c where a. id = c. id );
Commit;
Execution result:
1 2010 11
2 2010 12
3 2011 3
Method 2: The where condition uses exists
Update t_1 a set (a. year, a. month) = (select B. year, B. month from T_2 B where B. id = a. id)
Where exists (select 1 from T_2 B where B. id = a. id)
Commit;
Execution result:
Same method 1.
Method 3: cursor
Declare
Cursor target_cur is select year, month, id from t_2;
Begin
For my_cur in target_cur loop
Update t_1 set year = my_cur.year, month = my_cur.month
Where id = my_cur.id;
End loop;
End;
Execution result:
Execution result: Same as method 1.
Method 4:
Update (select. year aYear,. id aId,. month aMonth, B. year bYear, B. id bId, B. month bMonth from t_1 a, t_2 B where. id = B. id) set aYear = bYear, aMonth = bMonth;
Commit;
Execution result:
An oracle error ora-01779 was reported and could not be modified to save the corresponding column of the table for non-key values.

Solution: Set the IDs of the two tables to the primary keys of each table, and run the command again to obtain the correct result.
Alter table T_1 add constraint t1_key_id primary key (ID );
Alter table T_2 add constraint t2_key_id primary key (ID );

This article is from "mymailzxj"