🗄️

MySQLのUPSERT(マージ)SQL

2021/03/28に公開約3,800字

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

Discussion

ログインするとコメントできます