How to update data in another table with data from one table in Oracle

To build a table statement:

CREATE TABLE table1 (
IDD VARCHAR2 (10),
Val Varchar2 (20)
);
CREATE TABLE Table2 (
IDD VARCHAR2 (10),
Val Varchar2 (20)
);

Insert data:

INSERT INTO table1 values (' 01 ', ' 1111 ');
INSERT INTO table1 values (' 02 ', ' 222 ');
INSERT INTO table1 values (' 02 ', ' 2222 ');
INSERT INTO table1 values (' 03 ', ' 3333 ');
INSERT INTO table1 values (' 04 ', ' 4444 ');
INSERT INTO table1 values (' 06 ', ' 6666 ');
Commit
INSERT into table2 values (' n ', ' aaaa ');
INSERT into table2 values (' Up ', ' bbbb ');
INSERT into table2 values (' ', ' CCCC ');
INSERT into table2 values (' ', ' dddd ');
INSERT into table2 values (' + ', ' eee ');
INSERT into table2 values (' to ', ' eeee ');
Commit

2 tables are as follows:

The value of Idd-val in Table2 is assigned to table1 corresponding idd-val;

Attention:

• There are 2 IDD in Table1, which are different from 02,val;
• There is no 05,table1 in the table2;
• There are no 06,table2 in the Table1.

SQL statements:

1. Through subqueries, the update is updated directly, as follows:
   Update table1 Set table1.val = (select Val from table2 where Table1.idd = Table2.idd);
   

• Problem: For IDD exists in Table1, table2 does not exist, Val becomes null;

1. Improve, add restrictions, for Table1, but the table2 does not exist in IDD, do not modify;
   Update table1 Set val = (select Val from table2 where Table1.idd = Table2.idd)
   where exists (select 1 from table2 where Table1.idd = Table2.idd)
   

• However, the above 2 types of writing, encountered in table2 continue to insert data,
  INSERT into table2 values (' ', ' CCC ');
  That is, there is an IDD in table2 that corresponds to multiple Val, and there is a corresponding IDD in the Table1.
• Error after execution:
  ORA-01427: Single-line subquery returns multiple rows
  

1. Use merge, as follows:
   Merge into Table1
   Using table2
   On (Table1.idd = Table2.idd)
   When matched then
   Update Set table1.val = Table2.val

• There is an IDD in table2 that corresponds to multiple Val, and there is a corresponding IDD in the Table1, the error is as follows:
  
  ORA-30926: Unable to get a stable set of rows in the source table

1. On the basis of 3, the restriction condition is added;
   Merge into Table1
   Using (select T.idd, max (T.val) m from table2 T Group by T.idd) table2
   On (Table1.idd = Table2.idd)
   When matched then
   Update Set table1.val = TABLE2.M

• The above method constructs a new table2,group by IDD after using, but must deal with Val, if it is a varchar type, you can choose Max,min and so on, if the number type, you can use functions such as SUM,AVG, in short, To filter Val, the new table2 is an IDD corresponding to a val.

Reference: Update data for another table with data from one table in Oracle

How to update data in another table with data from one table in Oracle