转自:http://dikar.iteye.com/blog/797321
我们经常遇到的一个需求是,先判断数据是否存在,如果存在则更新,否则就插入,以前比较土是用java自己做了。这个在多线程或者多机的情况下就会有些问题,有时候还得让db报个唯一性约束才行。
最近和一个同事(以前是oracle的)做项目,发现他写了个牛逼的sql(或者说自己见识太短浅了),特此膜拜下
类似这样的
查了些资料发现这种merge sql早就被支持了,只是自己还不知道而已。
<--------------------------------------不华丽的分界线--------------------------------------------------------------------->
例如wiki上的 http://en.wikipedia.org/wiki/Merge_%28SQL%29
Merge (SQL)
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 ] |
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
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: