Abstract
2つのテーブルで、本テーブルと差分更新データテーブルがあって、マージしたいことがよくあります。MySQLでこの時に使うUPSERT/MERGE(マージ)を行うSQLを紹介します。
今回は、以下の想定でSQLを作成します。
| テーブル名 | 説明 |
|---|---|
| OS | 元のテーブル |
| OS_MODIFYING | 差分保持テーブル |
上記のテーブルでOSテーブルとOS_MODIFYINGテーブルを比較し、OSテーブルにレコードがなければINSERT、レコードがあればUPDATEを行います。
達人に学ぶDB設計徹底指南書 初級者で終わりたくないあなたへ [ ミック ]
OSテーブル(元テーブル)
以下のDDLでテーブルを作成しデータをINSERTします。
CREATE TABLE OS(
OS_VERSION VARCHAR(50),
OS_NAME VARCHAR(50),
DEV_CODE VARCHAR(50),
ANNOUNCEMENT_DATE DATE,
RELEASE_DATE DATE,
PRIMARY KEY(OS_VERSION)
);| OS_VERSION | OS_NAME | DEV_CODE | ANNOUNCEMENT_DATE | RELEASE_DATE |
|---|---|---|---|---|
| Classic Mac OS System 1 | NULL | NULL | NULL | 1984-01-24 |
| Mac OS X v10.0 | NULL | Cheetah | 2001-01-12 | 2001-03-24 |
| macOS v10.12 | Sierra | NULL | 2016-06-13 | 2016-09-20 |
| macOS v11 | Big Sur | NULL | 2020-06-22 | 2020-11-13 |
| macOS v12 | NULL | NULL | NULL | NULL |
| OS X v10.8 | Mountain Lion | Zinfandel | 2012-02-16 | 2012-07-25 |
| Rhapsody Developer Release | Windows Trouble | ME | 1111-11-11 | 1234-12-12 |
OS_MODIFYING(差分データテーブル)
OSテーブルと同じDDLでテーブルを作成。OS VERSIONが「1.0」をINSERTし「Rhapsody Developer Release」のレコードをUPDATEするようなデータを準備します。
CREATE TABLE OS_MODIFYING(
OS_VERSION VARCHAR(50),
OS_NAME VARCHAR(50),
DEV_CODE VARCHAR(50),
ANNOUNCEMENT_DATE DATE,
RELEASE_DATE DATE,
PRIMARY KEY(OS_VERSION)
);| OS_VERSION | OS_NAME | DEV_CODE | ANNOUNCEMENT_DATE | RELEASE_DATE |
|---|---|---|---|---|
| 1.0 | Windows 1.0 | NULL | NULL | 1985-11-20 |
| Rhapsody Developer Release | NULL | Grail1Z4 / Titan1U | NULL | 1997-08-31 |
UPSERT(マージ)SQL
OSレコードに対して、OS_MODIFYINGの「1.0」のレコードをINSERTし「Rhapsody Developer Release」をUPDATEします
INSERT INTO OS
WITH mdfy AS (SELECT * FROM OS_MODIFYING b WHERE b.RELEASE_DATE IS NOT NULL)
SELECT * FROM (SELECT * FROM mdfy) a
ON DUPLICATE KEY UPDATE
OS_VERSION = a.OS_VERSION,
OS_NAME = a.OS_NAME,
DEV_CODE = a.DEV_CODE,
ANNOUNCEMENT_DATE = a.ANNOUNCEMENT_DATE,
RELEASE_DATE = a.RELEASE_DATE;UPSERT(マージ)SQL実行結果
実行結果は以下の通り。
| OS_VERSION | OS_NAME | DEV_CODE | ANNOUNCEMENT_DATE | RELEASE_DATE |
|---|---|---|---|---|
| 1.0 | Windows 1.0 | NULL | NULL | 1985-11-20 |
| Classic Mac OS System 1 | NULL | NULL | NULL | 1984-01-24 |
| Mac OS X v10.0 | NULL | Cheetah | 2001-01-12 | 2001-03-24 |
| macOS v10.12 | Sierra | NULL | 2016-06-13 | 2016-09-20 |
| macOS v11 | Big Sur | NULL | 2020-06-22 | 2020-11-13 |
| macOS v12 | NULL | NULL | NULL | NULL |
| OS X v10.8 | Mountain Lion | Zinfandel | 2012-02-16 | 2012-07-25 |
| Rhapsody Developer Release | NULL | Grail1Z4 / Titan1U | NULL | 1997-08-31 |
OS VERSIONの「1.0」のレコードが追加されており、「Rhapsody Developer Release」が更新することができました。
