SQLite INSERT - ON DUPLICATE KEY UPDATE vs INSERT OR REPLACE vs MERGE

2024-04-02

SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT) 解説

INSERT ... ON DUPLICATE KEY UPDATE は、SQLite における INSERT ステートメントの拡張機能です。この機能を使うと、レコードが既に存在する場合、そのレコードを更新することができます。

動作

この機能は、以下の2つのステップで動作します。

INSERT ステートメントを実行し、レコードが存在するかどうかを確認します。
レコードが存在する場合は、ON DUPLICATE KEY UPDATE 句で指定された列を更新します。レコードが存在しない場合は、新しいレコードが挿入されます。

メリット

この機能を使うことで、以下のメリットがあります。

INSERT と UPDATE の両方の操作を1つのステートメントで記述できるため、コードが簡潔になります。
レコードの存在チェックと更新処理をまとめて行うことができるため、処理効率が向上します。

標準SQLではないため、他のデータベースでは使用できない場合があります。
複雑なステートメントになるため、理解やメンテナンスが難しくなる可能性があります。

使用例

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE
  email = '[email protected]';

この例では、users テーブルに John Doe という名前と [email protected] というメールアドレスを持つレコードが存在するかどうかを確認します。レコードが存在する場合は、メールアドレスを [email protected] に更新します。レコードが存在しない場合は、新しいレコードが挿入されます。

補足

ON DUPLICATE KEY UPDATE 句では、複数の列を更新することができます。

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE
  email = '[email protected]';

複数の列を更新する

INSERT INTO users (name, email, age)
VALUES ('John Doe', '[email protected]', 30)
ON DUPLICATE KEY UPDATE
  email = '[email protected]',
  age = age + 1;

WHERE 句を使用して更新対象となるレコードを絞り込む

INSERT INTO users (name, email)
VALUES ('John Doe', '[email protected]')
ON DUPLICATE KEY UPDATE
  email = '[email protected]'
WHERE country = 'USA';

INSERT OR REPLACE を使用する

INSERT OR REPLACE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

INSERT OR IGNORE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

ON DUPLICATE KEY UPDATE 以外の方法

INSERT OR REPLACE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

INSERT OR IGNORE INTO users (name, email)
VALUES ('John Doe', '[email protected]');

SELECT ... WHERE EXISTS を使用する

SELECT EXISTS (
  SELECT *
  FROM users
  WHERE name = 'John Doe'
  AND email = '[email protected]'
);

MERGE ステートメントを使用する

MERGE INTO users
USING (
  SELECT 'John Doe' AS name, '[email protected]' AS email
) AS t
ON users.name = t.name
AND users.email = t.email
WHEN MATCHED THEN
  UPDATE SET users.email = t.email
WHEN NOT MATCHED THEN
  INSERT (name, email) VALUES (t.name, t.email);