データベースで複数のケースを一度に済ませたい場合があります。
例えばデータベースの任意のテーブルにデータが存在していない場合には新規追加、データが存在していた場合には更新に切り替えるといったようなケースです。
これを実現させるには「merge」というSQL文を使用すれば解決できます。
そこで今回はこのmerge文について解説してみたいと思います。
このmarge文を使えるようになれば、データベース操作が一段と楽になりますので、しっかりと学習しましょう。
mergeとは
margeは、任意のテーブルに対象レコードが存在している場合にはUPDATE(更新)をし、存在していない場合にはINSERT(挿入)をするといった処理に使われる文です。
すぐに想像できるものとしては会員情報ではないでしょうか。
会員情報システムでは、会員Noや住所及び電話番号などを基点として、該当するレコードがあれば更新、なければ新規登録します。
この処理を完結にさせるために思いつく一連の流れは
「SELECT文を実行してレコードを取得」
↓
「IF文でデータの有無を判断する」
↓
「もし該当するレコードが存在すればUPDATEを実行」
↓
「該当するレコードが存在しなければINSERTを実行」
というような形でしょう。
このようにケースによって処理を切り替えることは、データベースを使ったアプリケーション開発ではよく使われる手段です。
しかしSQLの特性上、SELECT処理とUPDATE及びINSERT処理を一つのクエリで実行することは不可能なため、こういった場合にmergeを使います。
margeステートメントと記述方法
先ほども説明したように、mergeは[ INSERT、UPDATE、DELETE ]の対象となるテーブルとデータを比較して、該当レコードが存在する場合としない場合で分岐処理を行います。
この章ではこれらのステートメントに必要な構成要素について解説していきます。
MARGE [ INTO ]
INSERT, UPDATE, DELETE操作を行う対象のテーブルを指定します。
記述方法は次の通りです。
[操作対象のテーブル] AS [代替名]
USING
INSERT, UPDATE, DELETE操作を行う対象のテーブルと比較するデータを指定します。
この時、比較対象となるデータにはSELECTを使用します。
記述方法は次の通りです。
[比較対象となるデータ] AS [代替名]
ON
操作対象テーブルと比較対象となるデータの比較条件を指定します。
WHEN MATCHED THEN
UPDATEを記述する要素で、捜査対象テーブルにレコードが存在する場合の処理を指定します。
WHEN NOT MATCHED THEN
INSERTを記述する要素で、対象テーブルにレコードが存在しない場合の処理を指定します。
WHEN NOT MATCHED BY SOURCE THEN
DELETEを記述する要素で、比較対象となるデータにレコードが存在しない場合の処理を指定します。
margeの使い方
一通りmargeの説明が終わりましたのでここからは実際にどのように使うかについて解説していきます。
まずはサンプルテーブルの説明です。
// 社員テーブル MariaDB [sample]> SELECT * FROM employee; +----+----------+-----+--------+ | id | name | age | status | +----+----------+-----+--------+ | 1 | Arex | 30 | R | | 2 | Jackson | 23 | NR | | 3 | Jonathan | 21 | NR | | 4 | Noah | 25 | R | | 5 | Aiden | 50 | R | | 6 | Caden | 29 | NR | | 7 | Smith | 36 | R | | 8 | Palmer | 42 | R | | 9 | Thomas | 50 | R | | 10 | Walker | 33 | R | +----+----------+-----+--------+ 10 rows in set (0.000 sec) // 退職者テーブル MariaDB [sample]> SELECT * FROM retiree; +----+------+--------+-----+--------+ | id | code | name | age | status | +----+------+--------+-----+--------+ | 1 | 5 | Aiden | 40 | R | | 2 | 9 | Thomas | 50 | R | +----+------+--------+-----+--------+ 2 rows in set (0.000 sec)
二つのテーブルはそれぞれ社員と退職者のテーブルです。
退職者テーブルの”code”は社員テーブルの”id”を参照しています。
UPDATEが実行されるケース
以下は「退職者テーブルにデータが存在した場合、年齢を書き換える」といったケースのサンプルコードです。
MERGE INTO retiree AS RT USING (SELECT 5 AS id, 'Aiden' AS name, 50 AS age, 'R' AS status ) AS ET ON ( RT.id = ET.id ) WHEN MATCHED THEN UPDATE SET id = ET.id, name = ET.name, age = ET.age, status = ET.status WHEN NOT MATCHED THEN INSERT (id, name, age, status) VALUES ( ET.id, ET.name, ET.age, ET.status ) ; id name age status ---- --------- -------- ------------- 5 Aiden 50 R 9 Thomas 50 R
実行の結果、retireeテーブルには既にid=5のレコードが存在したため、対象テーブルであるretireeテーブルの該当レコードがが更新されていることが確認できます。
INSERTが実行されるケース
では次に「退職者テーブルにデータが存在しない為、新規追加される」といったケースのサンプルコードを見てみましょう。
MERGE INTO retiree AS RT USING (SELECT 3 AS id, 'Jonathan' AS name, 21 AS age, 'NR' AS status ) AS ET ON ( RT.id = ET.id ) WHEN MATCHED THEN UPDATE SET id = ET.id, name = ET.name, age = ET.age, status = ET.status WHEN NOT MATCHED THEN INSERT (id, name, age, status) VALUES ( ET.id, ET.name, ET.age, ET.status ) ; id name age status ---- --------- -------- ------------- 5 Aiden 50 R 9 Thomas 50 R 3 Jonathan 21 NR
実行の結果、retireeテーブルには対象テーブル(employee)のid=3に該当するレコードが存在しなかったため、操作対象テーブルであるretireeにデータが新規追加されていることが確認できます。
UPDATEとINSERTが1回のクエリで実行されるケース
UPDATEとINSERTを一つのクエリで同時処理させるためには、USING内で指定するSELECTの条件にWHEREを使うなどの方法があります。
UPDATE employee SET age = 45 WHERE id = 5 ; MERGE INTO retiree AS RT USING (SELECT id, name, age, status FROM employee WHERE age >= 40 ) AS ET ON ( RT.id = ET.id ) WHEN MATCHED THEN UPDATE SET RT.age = ET.age WHEN NOT MATCHED THEN INSERT ( id, name, age, status ) VALUES ( ET.id, ET.name, ET.age, ET.status ) ; id name age status ---- --------- -------- ------------- 5 Aiden 45 R 9 Thomas 50 R 8 Palmer 42 R
サンプル用にまず「id=5」に該当するレコードの年齢を45歳に書き換えた上で、mergeではUSINGで参照させるデータを選別して「age>=40」のレコードだけを対象としています。
こうすることで、元々retireeテーブルに存在していた２つのレコードは更新、更にretireeテーブルに存在しなかったレコードは追加という処理が同時に実行されています。
まとめ
いかがでしたか？今回はmerge文について解説してみました。
記述を省略できることはミスを減らすことにも直結します。
またSQLを使いこなせるようになればデータベースの設計もより上達しますので、こういった方法を数多く覚えていくことを心がけましょう。
