UPSERT (ON CONFLICT) dan Aplikasi Bulk INSERT

Data yang akan kita pakai — stock dan stock_in

UPSERT (perpaduan UPDATE dan INSERT —

operasi "update kalau ada, insert kalau tidak" dalam satu pernyataan) diimplementasikan lewat sintaks INSERT … ON CONFLICT(key) DO UPDATE.

Kalau baris bentrok dengan primary key atau batasan UNIQUE, update dijalankan; kalau tidak, baris itu langsung di-insert apa adanya.

Sebelum latihan, periksa definisi kolom dan data sampel dari kedua tabel — stock dan stock_in.

① Jalankan PRAGMA table_info(stock); untuk melihat nama kolom, tipe, dan primary key dari stock (UPSERT mengasumsikan sku sebagai primary key).

② Pratinjau kedua tabel dengan SELECT * FROM stock ORDER BY sku; dan SELECT * FROM stock_in;.

ON CONFLICT DO UPDATE — update kalau ada, insert kalau tidak

Menulis INSERT INTO table(...) VALUES (...) ON CONFLICT(key_col) DO UPDATE SET col = ... berarti: kalau baris yang akan kamu insert bentrok dengan kunci yang dideklarasikan di ON CONFLICT (primary key atau kolom UNIQUE), update DO UPDATE dijalankan; kalau tidak ada bentrokan, baris itu langsung di-insert.

Di dalam DO UPDATE, nama tabel khusus excluded memungkinkan kamu merujuk "nilai dari baris yang akan kamu insert".

Tulis qty = qty + excluded.qty dan, ketika bentrok, qty yang ada akan bertambah sebesar qty yang ingin kamu insert.

Pakai qty = excluded.qty untuk timpa dan qty = qty + excluded.qty untuk akumulasi, tergantung kebutuhan.

-- Bentrok dengan sku yang ada → DO UPDATE menambah qty
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- sku baru → tidak bentrok, hanya di-insert
INSERT INTO stock(sku, name, qty, price)
VALUES ('A006', 'Marker', 3, 120)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

SELECT sku, name, qty FROM stock WHERE sku IN ('A001', 'A006');

Bayangkan kebutuhannya: "pada proses kedatangan, tambahkan ke stok untuk sku yang sudah ada, dan daftarkan yang baru untuk sku yang belum dikenal." (Jalankan dengan benar dan penjelasannya muncul.)

① Sebelum UPSERT, jalankan SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku; untuk memastikan A001 ada dan A006 tidak ada.

② Untuk A001 yang sudah ada, UPSERT dengan name Pen, qty 15, price 80. Ketika bentrok, tambahkan nilai yang di-insert ke qty yang ada (bukan timpa).

③ Untuk A006 yang belum dikenal, jalankan UPSERT sejenis dengan name Stapler, qty 3, price 200 (tidak bentrok, jadi di-insert).

④ Terakhir, jalankan kembali SELECT yang sama dan konfirmasi bahwa A001 ditambahkan dan A006 di-insert.

DO NOTHING — tidak melakukan apa-apa ketika bentrok

Kalau kamu tidak ingin memperbarui maupun memasukkan — yaitu, "diam-diam lewatkan ketika bentrok" — gunakan ON CONFLICT(key) DO NOTHING.

Tanpa bentrokan baris di-insert; ketika bentrok baris diabaikan tanpa error.

Pada contoh di bawah, mencoba memasukkan A002 yang sudah ada dengan DO NOTHING akan bentrok dan diabaikan, sementara A007 yang baru akan di-insert.

-- Abaikan ketika bentrok (DO NOTHING)
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 mempertahankan nilai aslinya (qty 60 / price 250)
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

Bayangkan kebutuhannya: "ketika memasukkan banyak baris ke master stock, jangan pernah menimpa sku yang sudah ada dan hanya tambahkan yang belum dikenal."

① Sebelum UPSERT, jalankan SELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku; untuk memastikan A003 ada dan A007 tidak ada.

② Coba masukkan A003 yang sudah ada dengan name Clip, qty 0, price 0, memakai DO NOTHING supaya diabaikan ketika bentrok.

③ Tambahkan A007 yang belum dikenal dengan name Eraser, qty 50, price 90 memakai UPSERT DO NOTHING yang sama.

④ Terakhir, jalankan kembali SELECT yang sama dan konfirmasi A003 tidak berubah dan A007 ditambahkan.

UPSERT banyak baris — gabungkan bulk INSERT dengan ON CONFLICT

Ketika kamu menambahkan ON CONFLICT pada INSERT banyak baris — VALUES (...),(...),(...) dipisahkan koma — setiap baris secara independen "update kalau bentrok, insert kalau tidak".

Kamu bisa merefleksikan data kedatangan dalam satu pernyataan, menggantikan loop prosedural yang mencabang antara UPDATE dan INSERT per baris dengan satu SQL.

Bahkan di UPSERT banyak baris, excluded tetap berarti "nilai yang akan kamu insert untuk baris ini," jadi menulis qty = qty + excluded.qty menerapkan "tambahkan untuk baris yang ada, insert apa adanya untuk baris baru" per baris.

Latihan terakhir artikel ini adalah bulk UPSERT semua 3 baris stock_in (A001 / A004 / A006).

-- UPSERT banyak baris: baris yang ada mengakumulasi qty dan menyegarkan price; baris baru di-insert
INSERT INTO stock(sku, name, qty, price)
VALUES
  ('A002', 'Note', 10, 260),
  ('A005', 'Glue', 20, 190),
  ('A007', 'Ruler', 15, 90)
ON CONFLICT(sku) DO UPDATE
  SET qty = qty + excluded.qty,
      price = excluded.price;

SELECT sku, name, qty, price FROM stock ORDER BY sku;

Bayangkan kebutuhannya: "refleksikan semua 3 baris tabel kedatangan stock_in ke stock dalam satu pernyataan." Ini latihan terakhir artikel.

① Sebelum UPSERT, jalankan SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku; untuk memastikan A001 / A004 ada dan A006 tidak.

② Terhadap stock, insert 3 baris dalam satu INSERT — A001 (name Pen / qty 50 / price 80), A004 (name Tape / qty 100 / price 150), A006 (name Marker / qty 30 / price 120) — dan tulis bulk UPSERT dengan ON CONFLICT(sku) DO UPDATE yang menambahkan ke qty ketika bentrok.

③ Terakhir, taruh SELECT sku, name, qty FROM stock ORDER BY sku; dan konfirmasi A001 / A004 ditambahkan dan A006 baru ditambahkan.