Oracle does not match the modifications to the table match can be added (MERGE into)

The merge statement is a new syntax for merging the UPDATE and INSERT statements oracle9i. Through the merge statement, the other table is queried based on the join criteria of one table or subquery, and the connection condition matches the update, unable to match the execution insert. This syntax only needs a full table scan to complete the whole work, the execution efficiency is higher than insert+update. *//* syntax: MERGE [into [Schema.] table [T_alias] USING [schema.] {Table | view | subquery} [T_alias] On (condition) when matched and merge_update_clause when not matched then merge_insert_clause;*/copy code merge into Usersusi  Ng Doctoron (users.user_id = Doctor.doctorid) when matched and  update set users.user_name = Doctor.doctornamewhen not Matched then  insert  values    (Doctor.doctorid,     Doctor.doctorid,     ' 8736f1c243e3b14941a59ff736e1b5a8 ',     doctor.doctorname,     sysdate,     ' T ',     ',     Doctor.deptid,     ' b319dac7-2c5c-496a-bc36-7f3e1cc066b8 '); Copy code

When matched and then is working on if it can be matched

When the  matched then is not matched to the operation;
If necessary, you can add and modify them separately.

As follows: individually modified and added

This is the raw data for Table1 and table2.

Merge into TABLE1 t1using TABLE2 t2--(table2 can be replaced by a select sub-statement "using (SELECT * from Tbaletemo) t2") on (t1.dd1 = T2.AA1) whe N matched then--just modifies update set t1.dd2 = T2.aa2, t1.dd3 = T2.aa3;commit;

The above script is modified table1 if the corresponding data can be found in table2 (t1.dd1=t2. AA1 this is the corresponding condition)

The data after execution is

Merge into TABLE1 t1using TABLE2 t2on (t1.dd1 = t2.aa1)--when matched then--update Set t1.dd2 = T2.aa2, t1.dd3 = t2.aa3whe N not matched Theninsert (T1.DD1,T1.DD2) values (T2.AA1,T2.AA2); --Just add

　　

Oracle does not match the modifications to the table match can be added (MERGE into)