Banyak alur kerja aplikasi berujung pada satu pertanyaan canggung: data ini baru atau sudah ada? Pola klasik menjawabnya dengan dua langkah — SELECT untuk mengecek, lalu INSERT atau UPDATE sesuai hasilnya. Pola dua langkah tersebut rapuh: dua koneksi yang berjalan bersamaan bisa sama-sama lolos pemeriksaan lalu sama-sama mencoba INSERT, dan salah satunya gagal dengan galat duplicate key. MySQL menyediakan jalan yang lebih elegan: INSERT ... ON DUPLICATE KEY UPDATE, perintah yang menyisipkan baris bila kuncinya belum ada dan memperbarui baris bila kuncinya sudah ada — semuanya dalam satu statement. Pada Chapter ini kita akan membedah perintah ini, membaca tiga wajah output-nya yang terkenal membingungkan, lalu memakainya untuk sinkronisasi data multi-baris.

Menyiapkan Tabel Latihan #

Kita pakai satu tabel dengan primary key id dan satu baris awal sebagai baris “lama” yang akan kita tabrakan berkali-kali:

sql 
CREATE DATABASE belajar_dml;
USE belajar_dml;

Output yang Dihasilkan:

text 
Query OK, 1 row affected (0.00 sec)

Database changed
sql 
CREATE TABLE produk (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  harga DECIMAL(10,2) NOT NULL,
  stok INT NOT NULL DEFAULT 0
);

INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 120);

SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+----+------------+---------+------+
| id | nama       | harga   | stok |
+----+------------+---------+------+
|  1 | Buku Tulis | 4500.00 |  120 |
+----+------------+---------+------+
1 row in set (0.00 sec)

Baris dengan id 1 inilah pemicu kondisi duplicate key pada seluruh contoh kita. Batasan penulisan sejak awal: klausa ON DUPLICATE KEY UPDATE bereaksi terhadap PELANGGARAN kunci apa pun — primary key seperti di tabel ini, maupun unique key yang dipelajari pada chapter UNIQUE Constraint di Section Table Design.

Upsert Dasar: Sisipkan atau Perbarui #

Anggaplah toko cabang mengirim laporan stok harian, dan laporan hari ini menyebut Buku Tulis (id 1) kini berjumlah 150. Kita terima laporan itu sebagai upsert:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 150) ON DUPLICATE KEY UPDATE stok = 150;

Output yang Dihasilkan:

text 
Query OK, 2 rows affected (0.00 sec)

Mari kita bedah setiap bagiannya:

  1. Bagian 1 (INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 150)): Bagian ini adalah INSERT biasa dan lengkap. Server pertama-tama mencoba menyisipkan baris ini seperti INSERT pada umumnya.
  2. Bagian 2 (ON DUPLICATE KEY UPDATE stok = 150): Klausa ini adalah rencana cadangan. Bila penyisipan menabrak kunci yang sudah dipakai — di sini id 1 sudah dimiliki Buku Tulis — server tidak membatalkan statement dengan galat, melainkan beralih memperbarui baris lama yang bertabrakan tadi menggunakan penetapan di klausa ini.

Responsnya Query OK, 2 rows affected dan isi tabel kini:

sql 
SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text 
+----+------------+---------+------+
| id | nama       | harga   | stok |
+----+------------+---------+------+
|  1 | Buku Tulis | 4500.00 |  150 |
+----+------------+---------+------+
1 row in set (0.00 sec)

Stok Buku Tulis berubah dari 120 menjadi 150 tanpa statement UPDATE terpisah. Statement yang sama juga tetap bisa menyisipkan baris baru — laporan produk yang belum terdaftar, misalnya Pensil 2B dengan id 2:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (2, 'Pensil 2B', 3000.00, 150) ON DUPLICATE KEY UPDATE stok = 150;

Output yang Dihasilkan:

text 
Query OK, 1 row affected (0.00 sec)

Kali ini id 2 belum ada, jadi bagian INSERT berhasil langsung dan klausa ON DUPLICATE KEY UPDATE diabaikan sama sekali — seperti tidak pernah ditulis. Satu statement, dua kemungkinan jalur, dipilih otomatis oleh keadaan data. Kombinasi sisip-atau-perbarui inilah yang lazim disebut upsert.

Membaca Output: 1, 2, atau 0 Rows Affected #

Bagian yang paling sering membingungkan pemula — bahkan pengembang berpengalaman — adalah angka pada responsnya. Kita jalankan tiga statement berturut-turut dan membandingkan hasilnya:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 180) ON DUPLICATE KEY UPDATE stok = 180;
INSERT INTO produk (id, nama, harga, stok) VALUES (3, 'Penghapus', 2500.00, 90) ON DUPLICATE KEY UPDATE stok = 90;
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 180) ON DUPLICATE KEY UPDATE stok = 180;

Output yang Dihasilkan:

text 
Query OK, 2 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Statement pertama memperbarui Buku Tulis dari 150 ke 180 dan melaporkan 2 rows affected. Statement kedua menyisipkan Penghapus yang benar-benar baru dan melaporkan 1 row affected. Statement ketiga adalah laporan yang SAMA terkirim ulang: Buku Tulis sudah bernilai 180, penetapan stok = 180 tidak mengubah apa pun, dan responsnya 0 rows affected. Kondisi akhir tabelnya:

sql 
SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text 
+----+------------+---------+------+
| id | nama       | harga   | stok |
+----+------------+---------+------+
|  1 | Buku Tulis | 4500.00 |  180 |
|  2 | Pensil 2B  | 3000.00 |  150 |
|  3 | Penghapus  | 2500.00 |   90 |
+----+------------+---------+------+
3 rows in set (0.00 sec)

Ketiga angka tersebut punya makna tetap yang perlu kita hafalkan:

Kejadian pada barisRows affected
Baris baru berhasil disisipkan1
Baris lama diperbarui dan nilainya berubah2
Baris lama “diperbarui” tetapi nilainya sudah sama0

Angka 2 untuk satu baris yang diperbarui memang terasa aneh — MySQL menghitungnya demikian karena upsert yang berubah jalur dihitung seperti baris yang disisipkan lalu diperbarui. Angka 0 justru kabar baik: statement berhasil tanpa galat, datanya hanya sudah sesuai sejak awal. Justru itulah yang membuat pola ini aman dikirim ulang (idempoten): laporan yang sama dikirim dua kali tidak menghasilkan galat maupun perubahan ganda.

Merujuk Nilai Baru dengan Alias Baris #

Klausa ON DUPLICATE KEY UPDATE stok = 150 menuliskan nilai 150 dua kali — sekali di bagian VALUES, sekali di bagian UPDATE. Menulis nilai secara duplikat seperti itu melelahkan dan rawan salah ketik. Sejak MySQL 8.0.19, kita bisa memberi nama (alias) pada baris nilai yang kita kirim, lalu merujuk nilai-nilainya di klausa UPDATE:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4500.00, 220) AS baru ON DUPLICATE KEY UPDATE stok = baru.stok;

Output yang Dihasilkan:

text 
Query OK, 2 rows affected (0.00 sec)

Bagian AS baru memberi nama baru pada kelompok nilai (...) di depannya, sehingga baru.stok berarti “nilai stok yang barusan dikirim” yaitu 220. Nilai 220 kini hanya ditulis satu kali. Alias baris juga bisa dipakai untuk beberapa kolom sekaligus, dan respons 2 rows affected menandakan baris lama benar-benar berubah:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4600.00, 240) AS baru ON DUPLICATE KEY UPDATE harga = baru.harga, stok = baru.stok;

Output yang Dihasilkan:

text 
Query OK, 2 rows affected (0.00 sec)

Bentuk Lama VALUES() Sudah Deprecated #

Sebelum bentuk alias ada, cara merujuk nilai baru adalah fungsi VALUES(kolom) di dalam klausa UPDATE — dan bentuk lama ini masih sering kita temui di kode lama:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES (1, 'Buku Tulis', 4600.00, 260) ON DUPLICATE KEY UPDATE stok = VALUES(stok);
SHOW WARNINGS;

Output yang Dihasilkan:

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

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Statement ini masih berhasil memperbarui stok menjadi 260, tetapi perhatikan responsnya: 2 rows affected, 1 warning. Perintah SHOW WARNINGS yang dijalankan langsung setelahnya menampilkan warning 1287 — fungsi VALUES() di klausa ON DUPLICATE KEY UPDATE resmi deprecated sejak MySQL 8.0.20 dan akan dihapus di rilis mendatang. Kode baru yang kita tulis sebaiknya memakai alias baris; kode lama yang masih memakai VALUES() perlu dicatat sebagai utang migrasi, bukan ditiru.

Upsert Multi-Baris untuk Sinkronisasi #

Kekuatan sesungguhnya muncul saat upsert digabungkan dengan batch multi-baris dari chapter sebelumnya. Misalnya toko cabang mengirim tiga laporan sekaligus: dua produk lama dan satu produk baru. Satu statement menuntaskan semuanya:

sql 
INSERT INTO produk (id, nama, harga, stok) VALUES
  (1, 'Buku Tulis', 4700.00, 300),
  (2, 'Pensil 2B', 3000.00, 320),
  (4, 'Stipo', 6000.00, 55) AS lapor
ON DUPLICATE KEY UPDATE harga = lapor.harga, stok = lapor.stok;

Output yang Dihasilkan:

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

Responsnya 5 rows affected dengan rincian Records: 3 Duplicates: 2. Angka 5 adalah penjumlahan lintas baris: Buku Tulis dan Pensil 2B adalah baris lama yang berubah (masing-masing dihitung 2), sedangkan Stipo adalah baris baru (dihitung 1) — total 2 + 2 + 1. Baris Duplicates: 2 menghitung berapa baris dari batch yang menabrak kunci yang sudah ada. Kondisi akhir tabel:

sql 
SELECT * FROM produk ORDER BY id;

Output yang Dihasilkan:

text 
+----+------------+---------+------+
| id | nama       | harga   | stok |
+----+------------+---------+------+
|  1 | Buku Tulis | 4700.00 |  300 |
|  2 | Pensil 2B  | 3000.00 |  320 |
|  3 | Penghapus  | 2500.00 |   90 |
|  4 | Stipo      | 6000.00 |   55 |
+----+------------+---------+------+
4 rows in set (0.00 sec)

Kapan Kita Memakai Upsert #

Pola upsert bersinar pada tiga situasi. Pertama, sinkronisasi berkala seperti contoh kita: file laporan harian berisi campuran produk lama dan baru, dan satu statement upsert menuntaskan semuanya tanpa pemeriksaan awal. Kedua, pengiriman ulang yang aman: sistem antrian dan integrasi sering mengirim ulang pesan yang gagal terkonfirmasi, dan upsert membuat pengiriman ulang tersebut tidak menimbulkan galat maupun duplikasi. Ketiga, penghitung statistik: pola INSERT INTO pengunjung (tanggal, jumlah) VALUES ('2026-01-15', 1) ON DUPLICATE KEY UPDATE jumlah = jumlah + 1 membuat baris pertama kunjungan hari itu tersisipkan dan kunjungan berikutnya tinggal menambah angka — ekspresi di kanan tetap boleh membaca nilai lama seperti stok = stok + 30.

Upsert kurang cocok bila aplikasi butuh logika yang lebih kaya daripada “sisip atau perbarui” — misalnya kolom yang boleh berbeda antara jalur sisip dan jalur perbarui secara rumit, atau keputusan yang bergantung pada nilai lama sebelum ditimpa. Untuk kasus seperti itu, UPDATE eksplisit yang diperiksa hasilnya tetap lebih jernih. Satu peringatan teknis terakhir: upsert hanya bereaksi pada pelanggaran kunci; kesalahan lain seperti nilai NULL di kolom NOT NULL tetap menggagalkan statement secara biasa.

Membersihkan Database Latihan #

Latihan selesai dan database belajar_dml bisa dihapus:

sql 
DROP DATABASE belajar_dml;

Output yang Dihasilkan:

text 
Query OK, 1 row affected (0.00 sec)

Referensi #

Upsert memberi kita satu statement yang bijak memilih antara sisip dan perbarui, dengan kerusakan minimum terhadap baris lama. MySQL punya satu varian lagi yang tampak serupa tetapi jauh lebih brutal: REPLACE, yang menyingkirkan baris lama sepenuhnya lalu menyisipkan baris baru dari nol. Chapter berikutnya membahas REPLACE INTO, membuktikan bagaimana kolom yang tidak kita sebut ikut kehilangan nilainya, lalu membandingkannya dengan upsert supaya kita tahu kapan memilih yang mana.