MySQLのUPSERT(マージ)SQL
Abstract
データの移行作業などでCSVデータをLoaderなどで一時テーブルに取り込み、本番テーブルにマージするということはよくあると思います。
また、個人的に読みやすいSQLを作るため、With句をよく使います。
MySQLのWith句を使ったUPSERT(マージ)SQLを備忘録として以下に記載します。
達人に学ぶDB設計徹底指南書 初級者で終わりたくないあなたへ [ ミック ]
動作確認
環境
Docker上のMySQLを使用しました。
| 環境 | バージョン |
|---|---|
| Docker | mysql https://hub.docker.com/_/mysql |
| MySQL | mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL) |
DDL
以下の二つのテーブルがあるとします。
MACOSの内容を、OSというテーブルにマージします。
(なお、テーブルの内容は適当です)
| テーブル名 | 内容 |
|---|---|
| MACOS | 作業用一時テーブル |
| OS | 本番 |
MACOS
- 作業用一時想定テーブル
create table MACOS(
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 |
| Rhapsody Developer Release | NULL | Grail1Z4 / Titan1U | NULL | 1997-08-31 |
| Mac OS X v10.0 | NULL | Cheetah | 2001-01-12 | 2001-03-24 |
| OS X v10.8 | Mountain Lion | Zinfandel | 2012-02-16 | 2012-07-25 |
| 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
- 本番想定テーブル (マージされるテーブル)
create table MICROSOFT_WINDOWS(
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 | Windows 10 | NULL | 2014-09-30 | 2015-07-29 |
MySQLのUPSERT(マージ)SQL例
エイリアスを使えば、ON DUPLICATE KEY UPDATEで、INSERTしようとしたレコードを参照可能。
insert into OS
-- With句でマージしたいレコードを抽出
with released_macos as
(select * from MACOS where RELEASE_DATE is not null)
select * from (select * from released_macos) 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;
SQL実行結果
以下より「1.0」がINSERTされ、「Rhapsody Developer Release」がUPDATEされていることがわかる。
select * from OS order by 5;
| OS_VERSION | OS_NAME | DEV_CODE | ANNOUNCEMENT_DATE | RELEASE_DATE |
|---|---|---|---|---|
| Classic Mac OS System 1 | NULL | NULL | NULL | 1984-01-24 |
| 1.0 | Windows 1.0 | NULL | NULL | 1985-11-20 |
| Rhapsody Developer Release | NULL | Grail1Z4 / Titan1U | NULL | 1997-08-31 |
| Mac OS X v10.0 | NULL | Cheetah | 2001-01-12 | 2001-03-24 |
| OS X v10.8 | Mountain Lion | Zinfandel | 2012-02-16 | 2012-07-25 |
| macOS v10.12 | Sierra | NULL | 2016-06-13 | 2016-09-20 |
| macOS v11 | Big Sur | NULL | 2020-06-22 | 2020-11-13 |
Author And Source
この問題について(MySQLのUPSERT(マージ)SQL), 我々は、より多くの情報をここで見つけました https://zenn.dev/tay2501/articles/177289190d8d2d著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Collection and Share based on the CC protocol