Oracle9i新增的语法，用来合并UPDATE和INSERT语句。通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT+UPDATE。
MERGE INTO [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 MATCHED THEN [here you can execute some update sql or something else ]

　　WHEN NOT MATCHED THEN [execute something else here ! ]

----------------------------------------

MERGE INTO KEYWORD T1
                                       USING (SELECT :NAME AS NAME,:PIN_YIN AS PIN_YIN,:PIN_YIN_FIRST AS PIN_YIN_FIRST,1 As TIMES FROM dual) T2
                                       ON (T1.NAME=T2.NAME)
                                       WHEN MATCHED THEN
                                            UPDATE SET T1.TIMES = T1.TIMES + 1
                                       WHEN NOT MATCHED THEN
                                            INSERT(NAME,PIN_YIN,PIN_YIN_FIRST,TIMES) VALUES(T2.NAME,T2.PIN_YIN,T2.PIN_YIN_FIRST,T2.TIMES)

ITPub博客

Oracle语句自动判断是要更新记录还是要插入记录

最新文章