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

Preparation Phase

1. Table statement:

CREATE TABLE table1 (  
IDD varchar2 (a),  
Val varchar2 ())  
; 
CREATE TABLE table2 (  
IDD varchar2 (a),  
Val varchar2 ())  
;

2. Insert Data:

INSERT INTO table1 values (' ', ' 1111 ');
INSERT INTO table1 values (' ', ' 222 ');  
INSERT INTO table1 values (' ', ' 2222 ');    
INSERT INTO table1 values (' ", ' 3333 ');  
INSERT INTO table1 values (' ', ' 4444 ');
INSERT INTO table1 values (' ", ' 6666 ');
commit;  
INSERT into table2 values (' ', ' aaaa ');
INSERT into table2 values (' ', ' bbbb ');      
INSERT into table2 values (' ", ' cccc '); 
INSERT into table2 values (' ', ' dddd ');
INSERT into table2 values (' ', ' eee ');

3. Two tables are shown below:

To idd-val the value of the Table2, assign the value to the Table1 corresponding idd-val;
To verify the reasonableness of the operation, set the following several additional considerations:
Note that the two special places are: table1, there are 1 IDD field value of 06 data, table2 in the IDD field is not 06, named E1; table1, there are 2 IDD field values are 02, and the corresponding Val different data, named E2, The following are normal to resolve this situation; in table2, there are 2 IDD field values of 05, but the corresponding Val value of different data, named E3, to be added; SQL query: 1. The easiest way to think of it: through subqueries, direct update, as follows:

Update table1 Set table1.val = (select Val from table2 where Table1.idd = Table2.idd);

Problem: We have encountered the E1 situation, that is, the Table1 06 corresponding values have been changed-->val to null (that is, the blank in the graph);
This is not our intention, so we make the following improvements. 2. Add the restriction condition, for the value in the Table1, but the IDD field without a value in the table2, does not make the modification;

Update table1 Set val = (select Val from table2 where Table1.idd = Table2.idd)
where exists (select 1 from table2 wher E Table1.idd = Table2.idd)

The 2nd way of writing seems fine, but if we insert a piece of data into the table2 again,
INSERT into table2 values (' ', ' CCC '); Encountered E3 situation, after execution will be the following error:
ORA-01427: Single-line subquery returns multiple rows
3. Through the above analysis, simple UPDATE statements do not solve the abnormal situation encountered. So we can use the merge as follows:

Merge into table1
using  table2 in
(Table1.idd = Table2.idd) When
matched then
update set Table1.val = Table2.val

Although can solve the E1 situation, but encountered E3 situation, still error, as follows:

ORA-30926: Unable to get a stable set of rows in the source table 4. Finally, on the basis of 3, the restriction condition is added and can be solved;

Merge into Table1
using  (select T.idd, max (T.val) m from table2 T Group by T.idd) table2 on
(Table1.idd = table 2.idd) when
matched then
update set table1.val = TABLE2.M

The above method constructs a new table2 after using, but must make the processing to Val, if is the varchar type, may choose Max,min function, if the number type, may use the sum,avg and so on, in short, To make a deal with Val (for multiple times, exactly which.) The largest or smallest), the new table2 is a IDD corresponding to a val. Why the new table2 should be constructed by adding  group by t.idd&nbsp, because  select Max (t.val) m from table2 t  the query is a piece of data. T.idd does not belong to this data any fields, so select T.idd after the error, splicing group by T.idd can detect the need for the IDD field. (for Oracle databases, MySQL does not)