UPSERT in MySQL and Oracle

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

窮得只剩技術

MySQL

Oracle

SYSTEM.SYS_C009996 是 Primary key 名稱

看更多