Merge Sql

chenkehao

　　我们经常遇到的一个需求是，先判断数据是否存在，如果存在则更新，否则就插入，以前比较土是用java自己做了。这个在多线程或者多机的情况下就会有些问题，有时候还得让db报个唯一性约束才行。
　　
　　最近和一个同事（以前是oracle的）做项目，发现他写了个牛逼的sql（或者说自己见识太短浅了），特此膜拜下
　　
　　类似这样的
　　

MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE  b.status != 'VALID';

 　　查了些资料发现这种merge sql早就被支持了，只是自己还不知道而已。
　　
　　<--------------------------------------不华丽的分界线--------------------------------------------------------------------->
　　
　　例如wiki上的  http://en.wikipedia.org/wiki/Merge_%28SQL%29
　　

Merge (SQL)

From Wikipedia, the free encyclopedia

Jump to: navigation
,
search

It has been suggested that Upsert be merged into this article or section. (Discuss )

　　A relational database management system
uses SQL
MERGE
(upsert
) statements to INSERT

new records or UPDATE

existing records depending on whether or not a condition
matches. It was officially introduced in the SQL:2008
standard.

Contents [hide ] 1 Usage 2 Other non-standard implementations 3 References 4 External links

 
Usage


MERGE INTO table_name
USING table_reference
ON (condition
)
WHEN MATCHED THEN
UPDATE SET column1
= value1
[, column2
= value2
...]
WHEN NOT MATCHED THEN
INSERT (column1
[, column2
...]) VALUES (value1
[, value2 ...



Other non-standard implementations

　　Some other database management systems support this, or very similar behavior, through their own, non-standard SQL extensions.
　　MySQL
, for example, supports the use of INSERT ... ON DUPLICATE KEY UPDATE
syntax[
1]

which can be used to achieve the same effect. It also supports REPLACE INTO
syntax[
2]

, which first deletes the row, if exists, and then inserts the new one.
　　SQLite
's INSERT OR REPLACE INTO
works similarly.
　　Firebird
supports both MERGE INTO
and a single-row version, UPDATE OR INSERT INTO tablename (columns) VALUES (values) [MATCHING (columns)]
,
but the latter does not give you the option to take different actions
on insert vs. update (e.g. setting a new sequence value only for new
rows, not for existing ones.)

 
References

• 
  ^
  
  MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
  
• 
  ^
  
  MySQL 5.1 Reference Manual: 11.2.6 REPLACE Syntax
  

 
External links

• 
  HyperSQL (HSQLDB) documentation
  
• 
  Oracle 11g Release 2 documentation
  on MERGE
  
  
• 
  Firebird 2.1 documentation
  on MERGE
  
  
• 
  DB2 v9 MERGE statement
  
• 
  SQL Server 2008 documentation
  
• 
  H2 (1.2) SQL Syntax page
  

　　
　　<---------------------------------------------不华丽的分界线--------------------------------------------------------------->
　　同时也找了
　　http://www.oracle-base.com/articles/10g/MergeEnhancements10g.php
　　

MERGE Statement Enhancements in Oracle Database 10g
　　
Oracle 10g includes a number of amendments to the MERGE
statement making it more flexible.

• 
  Test Table
  
• 
  Optional Clauses
  
• 
  Conditional Operations
  
• 
  DELETE Clause
  

Test Table
　　
The following examples use the table defined below.

CREATE TABLE test1 AS
SELECT *
FROM   all_objects
WHERE  1=2;

Optional Clauses
　　
The MATCHED
and NOT MATCHED
clauses are now optional making all of the following examples valid.

-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status;

Conditional Operations
　　
Conditional inserts and updates are now possible by using a WHERE
clause on these statements.

-- Both clauses present.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE  b.status != 'VALID';
-- No matched clause, insert only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE  b.status != 'VALID';
-- No not-matched clause, update only.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID';

DELETE Clause
　　
An optional DELETE WHERE
clause can be used to clean up after a merge operation. Only those rows which match both the ON
clause and the
DELETE WHERE
clause are deleted.

MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE  b.status != 'VALID'
DELETE WHERE (b.status = 'VALID');

　　
For further information see:

• 
  MERGE