Oracle merge into usage details + instances

From: http://www.cnblogs.com/xy6521/articles/1310177.html

Oracle merge into usage details + instances

Purpose: merge into solves the problem of using Table B and new table a data. If table A does not exist, the data in Table B is inserted into Table; Syntax: Merge[Your table-name] [Rename your table here] Using([Write your query here]) [Rename your query-SQL and using just like a table] On([Conditional expression here] and [...]...) When mathed then[Here you can execute some update SQL or something else] When not mathed then[Execute something else here! ] ------------------------------------- Instance ----------------------------------------------------------------- merge into tfa_alarm_act_nms A using (select fp0, FP1, fp2, fp3, redefine_severity from tfa_alarm_status ) B On (. fp0 = B. fp0 and. FP1 = B. FP1 and. fp2 = B. fp2 and. fp3 = B. fp3) when matched then update set. redefine_severity = B. redefine_severity when not matched then insert (. fp0,. FP1,. fp2,. fp3, a.org _ severity,. redefine_severity,. event_time , a.int _ id) values (B. fp0, B. FP1, B. fp2, B. fp3, B. redefine_severity, B. redefine_severity, sysdate, 7777778); Role: Use a tableTfa_alarm_statusWith the new tableTfa_alarm_act_nmsB. redefine_severity,Condition:A. fp0 = B. fp0 and A. FP1 = B. FP1 and A. fp2 = B. fp2 and A. fp3 = B. fp3,IfTfa_alarm_act_nmsInsert data without this condition in the table. If your data volume is large, the SQL efficiency is very high.