UPSERT 是由 Update 和 Insert 所組成的
顧名思義就是用一道SQL指令
完成Insert或Update的動作
這通常用在不知道這筆資料新增過了沒
往往要先下一道SELECT確認之後
再用SELECT結果判斷要 Insert 還是 Update
而如果用 UPSERT 語法就可以用一道SQL完成這件事
但是現在沒有一個資料庫有做 UPSERT 這樣的關鍵字
都是用先 Insert 失敗再 Update
或是先 Update 看影響比數 再決定是否 Insert
像 MySQL 要達到UPSERT
就是先 Insert
MySQL
實際操作如下:
先建立一張測試表
CREATE TABLE student (
id int(11) PRIMARY KEY,
name VARCHAR(20)
);
MariaDB [sample_staff]> desc student;
+-------+-------------+------+-----+---------+-------+
' Field ' Type ' Null ' Key ' Default ' Extra '
+-------+-------------+------+-----+---------+-------+
' id ' int(11) ' NO ' PRI ' NULL ' '
' name ' varchar(20) ' YES ' ' NULL ' '
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
再建立一比資料
INSERT INTO student
(id, name)
VALUES
(1, 'Marry')
;
MariaDB [sample_staff]> SELECT * FROM student;
+----+-------+
' id ' name '
+----+-------+
' 1 ' Marry '
+----+-------+
1 row in set (0.00 sec)
接下來再新增一樣的資料會發生 Primary key 重複的錯誤
MariaDB [sample_staff]> INSERT INTO student (id, name) VALUES (1, 'Marry');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
這時要使用 MySQL ON Duplicate Update 的語法
13.2.5.2 INSERT … ON DUPLICATE KEY UPDATE Syntax
INSERT INTO student
(id, name)
VALUES
(1, 'John')
ON DUPLICATE KEY UPDATE
student.name = 'John'
;
MariaDB [sample_staff]> INSERT INTO student
-> (id, name)
-> VALUES
-> (1, 'John')
-> ON DUPLICATE KEY UPDATE
-> student.name = 'John'
-> ;
Query OK, 2 rows affected (0.01 sec)
MariaDB [sample_staff]> SELECT * FROM student;
+----+------+
' id ' name '
+----+------+
' 1 ' John '
+----+------+
1 row in set (0.00 sec)
可以看到名字被 update 成 John
但是 affected row 竟然是 2
這原因是他先 Insert 了一比
但不成功
再去 update 了那筆
所以受影響的資料就是兩筆
Oracle
至於 Oracle 可以用 MERGE 來達成
一樣先建立一張測試表
CREATE TABLE student (
id NUMBER(11) PRIMARY KEY,
name VARCHAR2(20)
);
建立一筆資料
INSERT INTO student
(id, name)
VALUES
(1, 'John');
如果重複新增一樣會出現錯誤
Error report -
ORA-00001: unique constraint (SYSTEM.SYS_C009996) violated
SYSTEM.SYS_C009996 是 Primary key 名稱
這時候把SQL改成 MERGE
MERGE INTO student
USING dual
ON
(student.id = 1)
WHEN NOT MATCHED THEN
INSERT
(id, name)
VALUES
(1, 'Marry')
WHEN MATCHED THEN
UPDATE
SET student.name = 'Marry'
;
再執行一遍
成功的訊息是
1 row merged.
代表資料重複會自動做 Update
而且不會先新增造成系統負擔
仔細看MERGE的語句可以發現
他也是先做了SELECT
如果有 id = 1 的資料
就 Update
如果沒有就 Insert