我们经常遇到的一个需求是，先判断数据是否存在，如果存在则更新，否则就插入，以前比较土是用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

1. ^ MySQL :: MySQL 5.1 Reference Manual :: 12.2.4.3 INSERT ... ON DUPLICATE KEY UPDATE Syntax
2. ^ 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

评论

1 楼 lionbule 2010-10-30  

*****