Merge Sql

回転元:http://dikar.iteye.com/blog/797321
私たちがよく出会う需要の一つは、まずデータが存在するかどうかを判断し、存在すれば更新し、そうでなければ挿入し、以前は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