RDBMSには重複する値を許可しないように、Primary KeyやUnique Key制約で担保する機能がありますが、バッチ処理などで重複を許可しないデータに対して、同じ値で更新する処理が発生する事があります。そんな時、データを入れ替える為に処理を複数行書く事はミスの原因にもなりますし、余計な処理が必要になるのでMySQLやPostgreSQLのようなRDBMSには上書き処理するSQLがあります。
MySQL
- Sample Tableとデータ
mysql> select * from members;
+----+------+-----+--------+----------------------+------------+
| id | name | age | salary | memo | regist |
+----+------+-----+--------+----------------------+------------+
| 1 | Mr.T | 20 | 100 | NULL | 2022-03-05 |
| 2 | Mr.U | 21 | 101 | NULL | 2022-03-05 |
| 3 | Mr.V | 22 | 102 | NULL | 2022-03-05 |
| 4 | Mr.S | 100 | 1000 | DUPLICATE KEY UPDATE | 2022-04-15 |
+----+------+-----+--------+----------------------+------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE members ADD CONSTRAINT UNIQUE idx_uq_members_name(name);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table members\G
*************************** 1. row ***************************
Table: members
Create Table: CREATE TABLE `members` (
`id` int NOT NULL,
`name` varchar(255) NOT NULL,
`age` int NOT NULL,
`salary` decimal(10,0) NOT NULL,
`memo` varchar(100) DEFAULT NULL,
`regist` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_uq_members_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ON DUPLICATE KEY UPDATE 句
INSERTではすでに、Primary Key, Unique Keyがあるので制約違反となりエラーになりますが、ON DUPLICATE KEY UPDATE 句を指定し、行を挿入すると、UNIQUE インデックスまたは PRIMARY KEY で値が重複する場合、古い行の UPDATE が実行される為エラーにはなりません。2 rows affectedとなっている事が確認する事が出来ます。
mysql> insert into members(id,name,age,salary,memo,regist) values(5,'Mr.Z',100,1100,'REPLACE句で入れ替え後',CURDATE());
ERROR 1062 (23000): Duplicate entry '5' for key 'members.PRIMARY'
mysql> insert into members(id,name,age,salary,memo,regist) values(5,'Mr.Z',100,1100,'REPLACE句で入れ替え後',CURDATE()) ON DUPLICATE KEY UPDATE memo = 'Using Duplicate Key Update';
Query OK, 2 rows affected (0.02 sec)※ InnoDB テーブルの場合、INSERT ステートメントは自動インクリメント値を増やしますが、UPDATE は増やしません。
ON DUPLICATE KEY UPDATE 句を指定し、行を挿入すると、UNIQUE インデックスまたは PRIMARY KEY で値が重複する場合、古い行の UPDATE が発生します。 たとえば、カラム a が UNIQUE として宣言され、値 1 を含んでいる場合、次の 2 つのステートメントには同様の効果があります。
13.2.6.2 INSERT … ON DUPLICATE KEY UPDATE ステートメント
REPLACE句
INSERTではすでに、Primary Key, Unique Keyがあるので制約違反となりエラーになりますが、Replaceでは該当データを削除後にInsertしているので制約違反にはなりません。2 Rows Affectedとなっている事からも確認する事が出来ます。
mysql> insert into members(id,name,age,salary,memo,regist) values(4,'Mr.S',100,1100,'REPLACE句で入れ替え',CURDATE());
ERROR 1062 (23000): Duplicate entry '4' for key 'members.PRIMARY'
mysql> replace into members(id,name,age,salary,memo,regist) values(4,'Mr.S',100,1100,'REPLACE句で入れ替え',CURDATE());
Query OK, 2 rows affected (0.02 sec)REPLACE は、テーブルに PRIMARY KEY または UNIQUE インデックスがある場合にのみ意味を持ちます。 それ以外の場合は、新しい行が別の行と重複するかどうかを判断するために使用されるインデックスがないため、INSERT と同等になります。
REPLACE は、INSERT とまったく同じように機能します。ただし、テーブル内の古い行に、PRIMARY KEY または UNIQUE インデックスに関して新しい行と同じ値が含まれている場合、その古い行は新しい行が挿入される前に削除されます。 セクション13.2.6「INSERT ステートメント」を参照してください。
13.2.9 REPLACE ステートメント
PostgreSQL
- Sample Tableとデータ
POC=# CREATE TABLE members (
id int NOT NULL,
name varchar(255) NOT NULL,
age int NOT NULL,
salary decimal(10,0) NOT NULL,
memo varchar(100) DEFAULT NULL,
regist date NOT NULL,
PRIMARY KEY (id)
) ;
CREATE TABLE
POC=# CREATE UNIQUE INDEX idx_uq_members_name on members(name);
CREATE INDEX
POC=# \d members;
Table "public.members"
Column | Type | Collation | Nullable | Default
--------+------------------------+-----------+----------+-------------------------
id | integer | | not null |
name | character varying(255) | | not null |
age | integer | | not null |
salary | numeric(10,0) | | not null |
memo | character varying(100) | | | NULL::character varying
regist | date | | not null |
Indexes:
"members_pkey" PRIMARY KEY, btree (id)
"idx_uq_members_name" UNIQUE, btree (name)
POC=# insert into members(id,name,age,salary,regist) values(1,'Mr.T',20,100,current_date);
INSERT 0 1
POC=# insert into members(id,name,age,salary,regist) values(2,'Mr.U',21,200,current_date);
INSERT 0 1
POC=# insert into members(id,name,age,salary,regist) values(3,'Mr.V',31,500,current_date);
INSERT 0 1
POC=# select * from members;
id | name | age | salary | memo | regist
----+------+-----+--------+------+------------
1 | Mr.T | 20 | 100 | | 2022-04-16
2 | Mr.U | 21 | 200 | | 2022-04-16
3 | Mr.V | 31 | 500 | | 2022-04-16
(3 rows)
UPSERT
INSERT処理は、PKの制約によりエラーになっていますが、CONFLICTした場合にUPDATE処理が指定されているので、対象のデータは更新されています。
POC=# SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name='members';
table_name | constraint_name | constraint_type
------------+-----------------------+-----------------
members | members_pkey | PRIMARY KEY
members | 2200_17544_1_not_null | CHECK
members | 2200_17544_2_not_null | CHECK
members | 2200_17544_3_not_null | CHECK
members | 2200_17544_4_not_null | CHECK
members | 2200_17544_6_not_null | CHECK
(6 rows)
POC=# insert into members(id,name,age,salary,memo,regist) values(2,'Mr.U',21,200,'Using Upsert',current_date);
ERROR: duplicate key value violates unique constraint "members_pkey"
DETAIL: Key (id)=(2) already exists.
POC=# insert into members(id,name,age,salary,memo,regist) values(2,'Mr.U',21,200,'Using Upsert',current_date)
POC-# ON CONFLICT ON CONSTRAINT members_pkey
POC-# DO UPDATE SET memo = 'Using Upsert';
INSERT 0 1
ON CONFLICT DO NOTHING
ON CONFLICT DO NOTHINGではconflict_targetを指定するのはオプションで、 省略すると利用可能なすべての制約（および一意インデックス）との競合が処理されます。 ON CONFLICT DO UPDATEではconflict_targetを指定しなければなりません。
insert into members(id,name,age,salary,memo,regist)
values(2,'Mr.U',21,200,'Using Upsert do nothing',current_date),
(4,'Mr.X',41,1200,'Using Upsert do nothing',current_date),
(5,'Mr.W',51,2200,'Using Upsert do nothing',current_date)
ON CONFLICT
DO NOTHING;以下の例では、既にConflictするデータがあるINSERT処理はそのままスルーし、Conflictするデータが無いINSERTのみ処理しています。よって、以下の例ではID4とID5のみが処理されています。
ON CONFLICT DO UPDATEはINSERTまたはUPDATEの原子的な結果を保証します。 無関係のエラーが発生しなければ、多数の同時実行がある状況においてさえも、それら2つの結果のうちの1つになります。 これはUPSERT、つまり「UPDATE or INSERT」としても知られています。
ON CONFLICT句