Upsert Patterns

Aplikasi nyata sering menulis data yang mungkin sudah ada: stok bertambah dari pengiriman barang yang bisa datang dua kali, pelanggan mendaftar lewat dua tab sekaligus, skrip migrasi dijalankan ulang karena gagal di tengah jalan. Pola naif “cek dulu dengan SELECT, baru INSERT” aman di dunia serial, tetapi runtuh ketika dua proses berjalan bersamaan. MySQL menyediakan satu keluarga statement yang menyelesaikan semuanya secara atomik: INSERT ... ON DUPLICATE KEY UPDATE — biasa disebut upsert karena menggabungkan update dan insert. Chapter ini membawa pola itu ke level lanjutan: alias baris untuk multi-baris, ekspresi delta stok, merge dari tabel sumber, idempotensi migrasi, lalu membandingkan dua saudaranya yang lebih berbahaya — INSERT IGNORE dan REPLACE — serta menutup dengan pola tahan-balapan di sisi aplikasi.

Menyiapkan Tabel Latihan #

Prasyarat mutlak semua pola upsert: tabel harus punya unique key, karena keputusan insert-atau-update diambil engine dari kegagalan memasukkan kunci duplikat. Kita pakai tabel produk dengan sku sebagai unique key dan stok sebagai kolom yang akan dimanipulasi:

sql
DROP DATABASE IF EXISTS belajar_adv;
CREATE DATABASE belajar_adv;
USE belajar_adv;

Output yang Dihasilkan:

text
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

sql
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sku VARCHAR(20) NOT NULL,
  nama VARCHAR(50) NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  diperbarui DATE NULL,
  UNIQUE KEY uq_sku (sku)
);

INSERT INTO produk (sku, nama, stok, diperbarui) VALUES
  ('KG-001', 'Kopi Gayo 250g', 10, DATE '2026-01-10'),
  ('TM-001', 'Teh Melati 100g', 5, DATE '2026-01-12');

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Rekap: Satu Statement untuk Insert dan Update #

Kita mulai dari mekanisme dasarnya. Dua statement berikut — satu untuk produk baru, satu untuk produk yang sudah ada — memakai bentuk yang identik, dan perbedaannya hanya terlihat pada angka baris yang “terkena efek”:

sql
INSERT INTO produk (sku, nama, stok) VALUES ('SM-001', 'Susu Merah 200g', 2)
AS baru
ON DUPLICATE KEY UPDATE stok = produk.stok + baru.stok;

INSERT INTO produk (sku, nama, stok) VALUES ('KG-001', 'Kopi Gayo 250g', 7)
AS baru
ON DUPLICATE KEY UPDATE stok = produk.stok + baru.stok,
                          diperbarui = DATE '2026-02-01';

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 1 row affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)

+----+--------+-----------------+------+------------+
| id | sku    | nama            | stok | diperbarui |
+----+--------+-----------------+------+------------+
|  1 | KG-001 | Kopi Gayo 250g  |   17 | 2026-02-01 |
|  2 | TM-001 | Teh Melati 100g |    5 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g |    2 | NULL       |
+----+--------+-----------------+------+------------+
3 rows in set (0.00 sec)

Statement pertama melaporkan 1 row affected: SM-001 tidak ada di tabel, jadi baris benar-benar disisipkan dengan id 3. Statement kedua melaporkan 2 rows affected: KG-001 sudah ada, engine gagal menyisipkan kunci duplikat lalu menjalankan cabang ON DUPLICATE KEY UPDATE — stok 10 + 7 = 17 dan tanggal diperbarui menjadi 2026-02-01. Angka 2 pada update bukan jumlah baris tabel yang berubah (tetap satu baris), melainkan konvensi pelaporan MySQL: satu untuk “insert berhasil” ditambah satu untuk “update dilakukan”. Ekspresi stok = produk.stok + baru.stok adalah pola delta — nilai lama ditambah nilai baru — yang akan kita bandingkan dengan pola absolut pada bagian idempotensi.

Alias Baris dan Referensi Terkualifikasi #

Klausa AS baru memberi nama pada baris-baris VALUES sehingga kolom nilai baru bisa dirujuk sebagai baru.stok — jauh lebih mudah dibaca daripada fungsi VALUES(stok) versi lama. Kekuatan alias terlihat pada upsert multi-baris, tetapi ada satu aturan keras yang menyertainya:

sql
INSERT INTO produk (sku, nama, stok, diperbarui) VALUES
  ('KG-001', 'Kopi Gayo 250g', 3, DATE '2026-02-05'),
  ('GA-001', 'Gula Aren 500g', 4, DATE '2026-02-05')
AS baru
ON DUPLICATE KEY UPDATE
  stok = produk.stok + baru.stok,
  diperbarui = baru.diperbarui;

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

+----+--------+-----------------+------+------------+
| id | sku    | nama            | stok | diperbarui |
+----+--------+-----------------+------+------------+
|  1 | KG-001 | Kopi Gayo 250g  |   20 | 2026-02-05 |
|  2 | TM-001 | Teh Melati 100g |    5 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g |    2 | NULL       |
|  5 | GA-001 | Gula Aren 500g  |    4 | 2026-02-05 |
+----+--------+-----------------+------+------------+
4 rows in set (0.00 sec)

Dua baris diproses dalam satu statement dengan keputusan per baris: KG-001 sudah ada (duplikat) sehingga stok naik 17 + 3 = 20 dan tanggalnya bergeser ke 2026-02-05; GA-001 belum ada sehingga disisipkan dengan stok 4. Total 3 rows affected = dua poin untuk update ditambah satu untuk insert. Perhatikan dua hal pada hasil: pertama, kolom nama milik KG-001 tidak berubah menjadi "x" atau nilai baru lainnya — hanya kolom yang disebut di bagian UPDATE yang tersentuh. Kedua, GA-001 menerima id 5, melompati 4 — setiap percobaan insert memesan satu nilai auto-increment, dan baris yang berbalik menjadi update tidak mengembalikan pesanannya. Id berlubang adalah perilaku terdokumentasi dan tidak membahayakan data.

Aturan kerasnya: begitu alias baris dipakai, setiap rujukan kolom wajib terkualifikasi. Menulis stok tanpa awalan menjadi ambigu karena nama itu kini dimiliki dua tabel sekaligus — tabel target produk dan kumpulan baris baru baru:

sql
-- @error
INSERT INTO produk (sku, nama, stok) VALUES ('KG-001', 'Kopi Gayo 250g', 5)
AS baru
ON DUPLICATE KEY UPDATE stok = stok + baru.stok;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

ERROR 1052 (23000): Column 'stok' in field list is ambiguous
Bye

Server menolak dengan ERROR 1052 — kolom stok ambigu. Perbaikannya satu macam: tulis produk.stok untuk nilai lama dan baru.stok untuk nilai baru, seperti contoh-contoh di atas.

INSERT … SELECT … ON DUPLICATE KEY UPDATE #

Sumber upsert tidak harus literal VALUES — data bisa mengalir dari tabel lain lewat INSERT ... SELECT ... ON DUPLICATE KEY UPDATE. Ini pola standar merge: data mentah masuk ke tabel staging, lalu digabungkan ke tabel utama satu statement:

sql
CREATE TABLE incoming (
  sku VARCHAR(20) PRIMARY KEY,
  nama VARCHAR(50),
  stok INT
);

INSERT INTO incoming (sku, nama, stok) VALUES
  ('KG-001', 'Kopi Gayo 250g', 2),
  ('RS-001', 'Kopi Robusta 250g', 12);

INSERT INTO produk (sku, nama, stok)
SELECT i.sku, i.nama, i.stok
FROM incoming i
ON DUPLICATE KEY UPDATE stok = produk.stok + i.stok;

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

+----+--------+-------------------+------+------------+
| id | sku    | nama              | stok | diperbarui |
+----+--------+-------------------+------+------------+
|  1 | KG-001 | Kopi Gayo 250g    |   22 | 2026-02-05 |
|  2 | TM-001 | Teh Melati 100g   |    5 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g   |    2 | NULL       |
|  5 | GA-001 | Gula Aren 500g    |    4 | 2026-02-05 |
|  7 | RS-001 | Kopi Robusta 250g |   12 | NULL       |
+----+--------+-------------------+------+------------+
5 rows in set (0.00 sec)

Tabel incoming menampung dua baris pengiriman. Statement merge membaca keduanya: KG-001 duplikat sehingga stok naik 20 + 2 = 22, sedangkan RS-001 baru sehingga disisipkan dengan stok 12 dan id 7 — sekali lagi id berlubang karena pemesanan auto-increment untuk baris duplikat hangus. Rujukan kolom mengikuti aturan join biasa: produk.stok untuk tabel target, i.stok untuk tabel sumber. Keunggulan pola ini dibanding menjalankan INSERT per baris dari aplikasi: seluruh merge adalah satu statement, sehingga bisa dibungkus satu transaksi dengan SELECT ... FOR UPDATE atau langsung diandalkan sebagai atomik per-statement tanpa bolak-balik jaringan.

Idempotensi: Delta atau Absolut #

Skrip migrasi dan pemulihan data punya satu ukuran keselamatan: boleh dijalankan berulang dengan hasil akhir yang sama — sifat idempoten. Pada upsert, sifat itu ditentukan oleh bentuk ekspresi update. Pola absolut stok = baru.stok menimpa ke nilai sumber, sehingga menjalankan ulang statement tidak mengubah apa pun; pola delta stok = produk.stok + baru.stok menambah terus setiap putaran. Kita buktikan dengan menjalankan pola absolut dua kali berturut-turut:

sql
INSERT INTO produk (sku, nama, stok) VALUES ('TM-001', 'Teh Melati 100g', 9)
AS baru
ON DUPLICATE KEY UPDATE stok = baru.stok;

INSERT INTO produk (sku, nama, stok) VALUES ('TM-001', 'Teh Melati 100g', 9)
AS baru
ON DUPLICATE KEY UPDATE stok = baru.stok;

INSERT INTO produk (sku, nama, stok) VALUES ('KG-001', 'Kopi Gayo 250g', 999)
AS baru
ON DUPLICATE KEY UPDATE stok = produk.stok;

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 2 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+--------+-------------------+------+------------+
| id | sku    | nama              | stok | diperbarui |
+----+--------+-------------------+------+------------+
|  1 | KG-001 | Kopi Gayo 250g    |   22 | 2026-02-05 |
|  2 | TM-001 | Teh Melati 100g   |    9 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g   |    2 | NULL       |
|  5 | GA-001 | Gula Aren 500g    |    4 | 2026-02-05 |
|  7 | RS-001 | Kopi Robusta 250g |   12 | NULL       |
+----+--------+-------------------+------+------------+
5 rows in set (0.00 sec)

Eksekusi pertama TM-001 melaporkan 2 rows affected — stok bergeser dari 5 menjadi 9. Eksekusi kedua yang identik melaporkan 0 rows affected: cabang update tetap dijalankan, tetapi nilai yang ditulis sama dengan nilai yang sudah ada sehingga tidak ada baris yang berubah. Statement ketiga memperlihatkan makna 0 rows affected yang lain: stok = produk.stok menulis nilai yang memang sudah ada — hasilnya tetap tidak ada perubahan. Inilah cara cepat mengaudit idempotensi: jalankan ulang skrip dan periksa apakah laporan affected rows turun menjadi nol. Delta punya tempatnya — data pengiriman yang memang terpisah per kejadian; absolut untuk muat ulang snapshot atau migrasi yang bisa diputar ulang.

INSERT IGNORE: Menelan Galat Diam-diam #

INSERT IGNORE adalah upsert versi brutal: baris yang menabrak kunci duplikat dilewati begitu saja, dan sisanya tetap dimasukkan. Tidak ada cabang update — baris lama menang mutlak:

sql
INSERT IGNORE INTO produk (sku, nama, stok) VALUES
  ('SM-001', 'Susu Merah PALSU', 99),
  ('SR-001', 'Sirup Markisa 300g', 6);

SHOW WARNINGS;

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 1

+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1062 | Duplicate entry 'SM-001' for key 'produk.uq_sku' |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

+----+--------+--------------------+------+------------+
| id | sku    | nama               | stok | diperbarui |
+----+--------+--------------------+------+------------+
|  1 | KG-001 | Kopi Gayo 250g     |   22 | 2026-02-05 |
|  2 | TM-001 | Teh Melati 100g    |    9 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g    |    2 | NULL       |
|  5 | GA-001 | Gula Aren 500g     |    4 | 2026-02-05 |
|  7 | RS-001 | Kopi Robusta 250g  |   12 | NULL       |
| 11 | SR-001 | Sirup Markisa 300g |    6 | NULL       |
+----+--------+--------------------+------+------------+
6 rows in set (0.00 sec)

Baris SM-001 ditolak karena duplikat — dan hanya tercatat sebagai warning, bukan error — sedangkan SR-001 tetap masuk. SHOW WARNINGS mengungkap baris yang hilang: Duplicate entry 'SM-001' for key 'produk.uq_sku'. Kekuatan dan bahayanya bertumpuk di tempat yang sama:

Aspek	Kapan sah	Kapan berbahaya
Baris duplikat	ingestion log/event — kejadian tercatat sekali, duplikat memang boleh dibuang	data master — baris “hilang” tanpa jejak error
Galat lain (konversi nilai, `NULL` di kolom wajib)	data longgar yang boleh tidak sempurna	menurunkan galat menjadi warning, masuk diam-diam
Audit	tindak lanjut dengan `SHOW WARNINGS` + hitung baris	warning diabaikan, selisih baris tak pernah dicek

Pola ON DUPLICATE KEY UPDATE hampir selalu lebih tepat untuk data bernilai: keputusan duplikat ditangani eksplisit, dan galat konversi tetap menjadi galat. INSERT IGNORE layak dipakai saat baris memang boleh hilang tanpa konsekuensi — pencatatan event analitik adalah contoh klasiknya.

REPLACE: Delete dan Insert Menyamar #

REPLACE terlihat seperti upsert — tabrakan kunci diselesaikan otomatis — tetapi mekanismenya sama sekali berbeda: baris lama dihapus, baris baru disisipkan. Kita buktikan dampaknya pada kolom id:

sql
CREATE TABLE produk_replace LIKE produk;
INSERT INTO produk_replace SELECT * FROM produk;

REPLACE INTO produk_replace (sku, nama, stok) VALUES ('KG-001', 'Kopi Gayo 250g', 20);

SELECT * FROM produk_replace ORDER BY id;

Output yang Dihasilkan:

text
Query OK, 0 rows affected (0.00 sec)

Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.00 sec)

+----+--------+--------------------+------+------------+
| id | sku    | nama               | stok | diperbarui |
+----+--------+--------------------+------+------------+
|  2 | TM-001 | Teh Melati 100g    |    9 | 2026-01-12 |
|  3 | SM-001 | Susu Merah 200g    |    2 | NULL       |
|  5 | GA-001 | Gula Aren 500g     |    4 | 2026-02-05 |
|  7 | RS-001 | Kopi Robusta 250g  |   12 | NULL       |
| 11 | SR-001 | Sirup Markisa 300g |    6 | NULL       |
| 12 | KG-001 | Kopi Gayo 250g     |   20 | NULL       |
+----+--------+--------------------+------+------------+
6 rows in set (0.00 sec)

Perhatikan baris KG-001 sebelum REPLACE: ia berdiri di id 1 dengan diperbarui terisi. Setelah REPLACE, baris itu lenyap dan baris pengganti muncul di ujung daftar dengan id 12 — id baru hasil insert segar — sementara kolom diperbarui yang tidak disebut dalam daftar nilai ter-reset menjadi NULL. Perilaku delete-then-insert ini punya efek berantai: FOREIGN KEY dengan ON DELETE CASCADE ikut menghapus baris anak di tabel lain, trigger DELETE dan INSERT terpicu berurutan, dan id lama tidak pernah dipertahankan. Untuk kebutuhan “perbarui bila ada”, ON DUPLICATE KEY UPDATE hampir selalu jawaban yang benar; REPLACE hanya pantas saat menimpa total memang diinginkan.

Balapan Cek-atau-Insert Antar-Proses #

Pola yang paling sering ditulis aplikasi pemula adalah cek-lalu-insert: SELECT untuk mengetahui baris ada atau tidak, lalu INSERT bila kosong. Di dunia nyata dua proses bisa menjalankan urutan yang sama nyaris bersamaan:

text
Waktu  Proses A                                  Proses B
----   ---------------------------------------   ---------------------------------------
t1     SELECT ... WHERE sku='SK-10'  → kosong
t2                                                SELECT ... WHERE sku='SK-10'  → kosong
t3     INSERT 'SK-10'  → sukses
t4                                                INSERT 'SK-10'  → ERROR 1062 duplicate

Kedua proses membaca tabel pada t1/t2 saat baris memang belum ada, lalu keduanya menyisipkan — salah satunya gagal dengan ERROR 1062. Race ini bukan bug MySQL; urutan baca-tulis antar dua koneksi memang tidak saling menunggu. Ada tiga jalan keluar yang benar. Pertama, serahkan ke engine: satu statement INSERT ... ON DUPLICATE KEY UPDATE bersifat atomik — tabrakan kunci ditangani di dalam engine, tanpa jendela kosong antara cek dan insert. Kedua, tangkap galat: biarkan INSERT polos mengembalikan ERROR 1062 ke aplikasi, tangkap error itu, lalu jalankan cabang update — kode menjadi:

text
try:
    INSERT INTO produk (sku, nama, stok) VALUES ('SK-10', 'Sirup', 6)
    → baris baru
catch Duplicate entry (1062):
    UPDATE produk SET stok = stok + 6 WHERE sku = 'SK-10'
    → baris sudah ada, proses lain lebih dulu

Ketiga, kunci eksplisit: SELECT ... FOR UPDATE pada baris atau LOCK TABLES/transaksi serial memaksa proses kedua menunggu — solusi paling berat yang hanya dibutuhkan bila keputusan bergantung pada banyak kolom sekaligus. Ketiganya berbagi prinsip yang sama: jangan pernah menaruh keputusan insert-atau-update pada dua statement terpisah yang bisa diselingi proses lain.

Membaca Angka Affected Rows #

Ringkasan hukum angka untuk INSERT ... ON DUPLICATE KEY UPDATE:

Nilai	Makna	Contoh di chapter ini
`1`	baris baru disisipkan	`SM-001` pada rekap dasar
`2`	baris duplikat di-update	`KG-001` delta pertama
`0`	cabang update dijalankan, nilai tidak berubah	absolut yang diulang

Aplikasi yang menghitung keberhasilan dari affected rows harus ingat satu pengecualian: dengan bendera koneksi CLIENT_FOUND_ROWS (diaktifkan lewat opsi koneksi, misal FOUND_ROWS pada connector), MySQL mengubah konvensi — update yang menimpa nilai sama dilaporkan 1 (baris ditemukan) alih-alih 0. Java JDBC dan beberapa ORM menyalakan bendera ini secara bawaan, sehingga angka yang sama bisa berbeda antar aplikasi. Saat memigrasi logika antar stack, periksa dulu bendera konektor sebelum membandingkan angka affected rows.

Membersihkan Database Latihan #

Rangkaian latihan selesai dan database latihan kita jatuhkan kembali:

sql
DROP DATABASE belajar_adv;

Output yang Dihasilkan:

text

Query OK, 3 rows affected (0.00 sec)

Referensi #

Menulis data kini aman untuk diputar ulang dan tahan balapan: upsert beralias, delta dan absolut yang dipilih sadar, merge satu statement, serta dua saudara berbahaya yang kita kenali batasnya. Chapter berikutnya menggeser fokus kembali ke pembacaan data dengan salah satu teka-teki tertua SQL: gap and island — membagi baris berurutan menjadi kelompok-kelompok pulau, dari deret angka klasik sampai sesi login dan status mesin.