UPSERT SQLite: ON CONFLICT DO UPDATE dan DO NOTHING

Insert, atau Update Jika Sudah Ada

Kebutuhan yang umum: masukkan sebuah baris, tapi jika baris dengan kunci yang sama sudah ada, perbarui baris itu saja. Tanpa UPSERT, kamu harus menulis SELECT dulu, lalu bercabang ke INSERT atau UPDATE: dua kali bolak-balik ke database dan ada race condition di antaranya.

UPSERT di SQLite melakukannya dalam satu pernyataan:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products;

Saat pertama kali dijalankan, barisnya dimasukkan. Jalankan lagi dengan harga berbeda dan sku yang sama, dan baris yang ada diperbarui di tempat. Tidak ada duplikat, tidak ada error.

Anatomi ON CONFLICT

Bentuk lengkapnya:

INSERT INTO table (...) VALUES (...)
ON CONFLICT(conflict_target) DO UPDATE SET col = expr, ...
WHERE condition;

Ada tiga bagian penting:

• conflict_target: kolom atau kolom-kolom dengan constraint UNIQUE atau PRIMARY KEY yang kamu perkirakan akan bertabrakan. SQLite memakai ini untuk memilih index mana yang diawasi.
• DO UPDATE SET ...: apa yang diubah pada baris yang ada saat tabrakan terjadi. (Atau DO NOTHING untuk melewatinya diam-diam.)
• WHERE opsional: kondisi tambahan yang harus benar agar update benar-benar dijalankan.

Target konflik harus cocok dengan constraint unik yang sungguhan. ON CONFLICT(price) tidak akan bisa dikompilasi jika price tidak unik, karena SQLite tidak punya apa pun untuk mendeteksi konflik.

DO NOTHING: Insert Jika Belum Ada, Selain Itu Lewati

Varian yang lebih sederhana. Berguna saat kamu mengisi data awal atau mencatat peristiwa dan duplikat cukup diabaikan diam-diam:

CREATE TABLE seen_events (
    event_id TEXT PRIMARY KEY,
    seen_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-1')
ON CONFLICT(event_id) DO NOTHING;

INSERT INTO seen_events (event_id) VALUES ('evt-2')
ON CONFLICT(event_id) DO NOTHING;

SELECT * FROM seen_events;

Insert kedua menabrak event_id yang sama dan biasanya akan memunculkan UNIQUE constraint failed. Dengan DO NOTHING, SQLite cukup melewatinya. Tidak ada exception, tidak ada baris yang terpengaruh.

Inilah "insert idempoten" yang sering dikerjakan orang dengan INSERT OR IGNORE. DO NOTHING milik UPSERT melakukan tugas yang sama dan lebih mudah dipadukan dengan klausa WHERE dan RETURNING.

Tabel Semu excluded

Saat konflik terjadi, tiba-tiba ada dua baris yang terlibat: baris yang sudah ada di tabel, dan baris baru yang coba kamu masukkan. SQLite memberimu cara untuk membicarakan keduanya.

• Nama kolom tanpa awalan (price, name) merujuk ke baris yang sudah ada.
• excluded.column merujuk ke baris masuk yang ditolak.

CREATE TABLE inventory (
    sku      TEXT PRIMARY KEY,
    quantity INTEGER NOT NULL,
    updated  TEXT
);

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 5, '2026-04-01')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

INSERT INTO inventory (sku, quantity, updated)
VALUES ('A-100', 3, '2026-04-02')
ON CONFLICT(sku) DO UPDATE SET
    quantity = quantity + excluded.quantity,
    updated  = excluded.updated;

SELECT * FROM inventory;

quantity = quantity + excluded.quantity dibaca sebagai "kuantitas yang ada ditambah kuantitas yang baru." Setelah dua insert, A-100 punya kuantitas 8. Pola ini, yaitu mengakumulasi nilai ke baris yang sudah ada, adalah salah satu trik UPSERT yang paling berguna.

UPSERT Bersyarat dengan WHERE

WHERE di bagian akhir memungkinkanmu melewati update kecuali suatu kondisi terpenuhi. Kondisi ini dievaluasi terhadap baris yang sudah ada (dan bisa merujuk ke excluded.* untuk baris yang masuk):

CREATE TABLE prices (
    sku        TEXT PRIMARY KEY,
    price      REAL NOT NULL,
    updated_at TEXT NOT NULL
);

INSERT INTO prices VALUES ('A-100', 10.00, '2026-04-01');

INSERT INTO prices (sku, price, updated_at)
VALUES ('A-100', 12.00, '2026-03-15')
ON CONFLICT(sku) DO UPDATE SET
    price      = excluded.price,
    updated_at = excluded.updated_at
WHERE excluded.updated_at > prices.updated_at;

SELECT * FROM prices;

Baris baru membawa updated_at yang lebih lama, jadi WHERE bernilai false dan update dilewati. Baris yang ada tetap memakai harganya yang lebih baru. Tukar tanggalnya, dan update akan berjalan. Inilah pola standar "hanya timpa dengan data yang lebih baru".

Upsert Banyak Baris

VALUES bisa berisi banyak baris, dan ON CONFLICT berlaku untuk masing-masing secara terpisah:

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

INSERT INTO products VALUES ('A-100', 'Widget', 9.99);

INSERT INTO products (sku, name, price) VALUES
    ('A-100', 'Widget v2', 10.99),
    ('A-200', 'Gadget',     14.99),
    ('A-300', 'Gizmo',      19.99)
ON CONFLICT(sku) DO UPDATE SET
    name  = excluded.name,
    price = excluded.price;

SELECT * FROM products ORDER BY sku;

A-100 bertabrakan dan diperbarui. A-200 dan A-300 baru, jadi dimasukkan. Satu pernyataan, hasil campuran insert dan update. Ini cara yang rapi untuk menyinkronkan sekumpulan data dari sumber eksternal.

UPSERT vs INSERT OR REPLACE

INSERT OR REPLACE terlihat seperti melakukan hal yang sama. Padahal tidak.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

-- Replace: deletes the old row, inserts a new one.
INSERT OR REPLACE INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com');

SELECT * FROM users;

notes hilang. INSERT OR REPLACE menghapus baris 1 seluruhnya dan memasukkan baris baru: setiap kolom yang tidak kamu cantumkan kembali ke NULL atau nilai default-nya. Perintah ini juga memicu trigger DELETE dan merambat lewat foreign key ON DELETE.

UPSERT mempertahankan barisnya:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT,
    notes TEXT
);

INSERT INTO users VALUES (1, 'Ada', 'ada@example.com', 'VIP customer');

INSERT INTO users (id, name, email)
VALUES (1, 'Ada Lovelace', 'ada@example.com')
ON CONFLICT(id) DO UPDATE SET
    name  = excluded.name,
    email = excluded.email;

SELECT * FROM users;

notes masih ada. Hanya kolom yang disebut di SET yang berubah. Jadikan UPSERT pilihan default; pakai INSERT OR REPLACE hanya saat kamu benar-benar menginginkan semantik hapus lalu masukkan ulang.

Beberapa Target Konflik

Jika sebuah baris bisa bertabrakan pada lebih dari satu constraint, kamu bisa merangkai beberapa klausa ON CONFLICT:

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email    TEXT UNIQUE,
    score    INTEGER DEFAULT 0
);

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 10)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

INSERT INTO accounts (username, email, score)
VALUES ('rosa', 'rosa@example.com', 5)
ON CONFLICT(username) DO UPDATE SET score = score + excluded.score
ON CONFLICT(email)    DO UPDATE SET score = score + excluded.score;

SELECT * FROM accounts;

Constraint mana pun yang terpicu lebih dulu yang menang, dan DO UPDATE milik cabang itu yang dijalankan. Dalam praktiknya, sebagian besar tabel punya satu target konflik yang jelas, yaitu primary key atau satu kolom unik, dan kamu jarang butuh lebih dari satu klausa.

Jebakan Umum

Beberapa hal yang sering menjebak:

• Tanpa unique index yang cocok, tidak ada UPSERT. ON CONFLICT(col) mengharuskan col berupa PRIMARY KEY atau punya constraint UNIQUE. Jika tidak, SQLite memunculkan error "no such constraint."
• DO UPDATE tidak berjalan jika tidak ada konflik. Ini adalah alternatif dari insert, bukan perilaku tambahan. Saat sebuah kunci pertama kali muncul, hanya insert yang berjalan.
• excluded hanya bisa dibaca. Kamu bisa membaca darinya, tapi tidak bisa menulis ke sana. Target SET selalu baris yang sudah ada.
• Rowid INTEGER PRIMARY KEY yang dibuat otomatis. Jika kamu tidak memberikan id, setiap insert mendapat id baru, jadi tidak ada yang bisa berkonflik. UPSERT hanya masuk akal saat kolom yang berkonflik punya nilai pasti yang diberikan oleh pemanggil.

Berikutnya: RETURNING

UPSERT tidak memberi tahu baris mana yang dimasukkan dan mana yang diperbarui, atau seperti apa nilai akhirnya. Untuk itu, kamu butuh klausa RETURNING: klausa ini mengembalikan baris yang terpengaruh dalam pernyataan yang sama, tanpa perlu SELECT susulan. Itu yang berikutnya.