Oracle uses the merge into statement to update data

/*merge into detailed introduction
The merge statement is a new syntax for merging the UPDATE and INSERT statements oracle9i.
With the merge statement, another table is queried based on the join criteria of one table or subquery,
The connection condition matches the execution insert on the update that cannot be matched.
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; */

Example:

Merge intousersusing Doctor on(Users.user_id =Doctor.doctorid) whenMatched Then  Update SetUsers.user_name =Doctor.doctorname when  notMatched Then  Insert  Values(Doctor.doctorid, Doctor.doctorid,'8736f1c243e3b14941a59ff736e1b5a8', Doctor.doctorname, Sysdate,'T',     ' ', Doctor.deptid,'B319dac7-2c5c-496a-bc36-7f3e1cc066b8'); Copy Code

A second reprint:

Summary of merge into usage in Oracle

causes :

Some time ago, because it involves a table of big data operations, to make additions and deletions at the same time, I and master think a lot of optimization methods, the results are unsatisfactory. Just started to use the original algorithm, first update the existing records, and then insert the other records to meet the requirements, and finally delete the extra records, but a small amount of data can also, 10W data is not, the foreground of the time-out is 60s, far from meeting the requirements. And then find a way to split the task, according to each record serial number of the tail character split, with multiple threads at the same time, has been split into 10 tasks (tail characters are 0, 1, 2, 3 ... 9), with 10 threads at the same time to execute, but the time is still very slow, up to processing 13W data around, 50W data is unbearable. Finally helpless had to the previous optimization all shovel off, instead of merge into operation, and then do some optimization, the single-threaded 50W data time-consuming shortened to more than 30 seconds, hereby, the usage of merge into is summarized as follows: (http://blog.csdn.net/yuzhic/ article/details/1896878)

There is a table T, there are two fields A, B, we want to do insert/update in table T, and if the condition is met, update the value of B in T, or insert a record in T. In Microsoft's SQL syntax, a simple sentence can be judged, and the syntax in SQL Server is as follows:

if exists(Select 1  fromTwhereT.a='1001' )    UpdateTSett.b=2 WhereT.a='1001' Else     Insert  intoT (A, B)Values('1001',2);

In Oracle, to achieve the same functionality, merge into is implemented (the functionality introduced by Oracle 9i) with the following syntax:

MERGE intotable_name ALIAS1 USING (Table|View|sub_query) Alias2 on(Joincondition) whenMatched Then     UPDATEtable_nameSETCol1=Col_val1, col2=Col_val2 when  notMatched Then     INSERT(column_list)VALUES(column_values);

Strictly speaking,"in a merge statement that has both the INSERT and update syntax, the total number of insert/update records is the number of records ALIAS2 in the using statement." so, to implement the above function, you can write this:

MERGE intoT t1using (SELECT '1001'  asA2  asB fromdual) T2 on(t1.a=t2.a) whenMatched Then    UPDATE SETt1.b=t2.b when  notMatched Then     INSERT(A, B)VALUES(t2.a,t2.b);

Oracle uses the merge into statement to update data