Merge Sql
我们经常遇到的一个需求是，先判断数据是否存在，如果存在则更新，否则就插入，以前比较土是用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
WHEREb.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHEREb.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)
,
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
WHERE1=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
WHEREb.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHEREb.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)
WHEREb.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
WHEREb.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
WHEREb.status != 'VALID'
DELETE WHERE (b.status = 'VALID');
For further information see:
[*]
MERGE
页:
[1]