UPDATE / DELETE dengan Subquery dan JOIN

Data yang akan kita pakai — stock dan stock_in

Artikel ini membahas gaya penulisan yang lebih lanjut — memperbarui dan menghapus dengan nilai dari tabel lain atau hasil agregat.

Spesifiknya, tiga pola: update yang nilainya dihitung oleh correlated subquery (subquery yang SELECT internalnya merujuk kolom dari baris luar, sehingga dievaluasi per baris), update bergaya join via UPDATE … FROM terhadap tabel lain, dan pernyataan DELETE dengan subquery di klausa WHERE.

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

① Jalankan PRAGMA table_info(stock); untuk memeriksa nama kolom, tipe, dan primary key dari stock.

② Pratinjau kedua tabel dengan SELECT * FROM stock LIMIT 5; dan SELECT * FROM stock_in;. Perhatikan bahwa stock_in berisi sku yang tidak ada di sisi stock.

Pakai correlated subquery untuk update dengan nilai dari tabel lain

Menulis UPDATE table SET col = (SELECT ... WHERE subquery.key = table.key) mengevaluasi SELECT internal sekali untuk setiap baris target dan menulis hasilnya ke kolom.

Karena SELECT internalnya merujuk kolom dari target UPDATE luar (stock.sku), bentuk ini disebut correlated subquery.

Jebakannya adalah: untuk baris stock yang tidak punya baris yang cocok di stock_in, SELECT internal mengembalikan NULL.

-- Tanpa COALESCE: qty ditimpa NULL untuk sku yang tidak punya pasangan
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

Kalau kamu menjalankan qty = qty + (SELECT ...) apa adanya, baris yang tidak punya kedatangan akan tertulis NULL pada qty-nya.

Untuk mencegah ini, bungkus subquery dengan COALESCE(subquery, 0) agar NULL berubah jadi 0, atau persempit target dengan WHERE EXISTS (...) supaya kamu hanya menyentuh baris yang benar-benar punya kedatangan.

-- Pratinjau dulu nilai pasca-update dengan SELECT (stock belum berubah)
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- Setelah puas, ganti ke UPDATE (COALESCE mengubah NULL jadi 0)
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

Bayangkan kebutuhannya: "refleksikan jumlah dari tabel kedatangan stock_in ke qty baris stock yang cocok." (Jalankan dengan benar dan penjelasannya akan muncul.)

① Pertama jalankan SELECT sku, qty FROM stock ORDER BY sku; untuk melihat keadaan sebelum update apa pun.

② Tulis versi COALESCE: update correlated subquery yang menambahkan jumlah stock_in.add_qty ke stock.qty. Untuk sku yang tidak punya baris pasangan di stock_in, perlakukan NULL sebagai 0 supaya nilainya tetap. Jalankan SELECT lagi setelahnya untuk mengonfirmasi.

③ Lalu tulis versi EXISTS: update yang menggunakan WHERE EXISTS (...) untuk membatasi target ke "baris yang punya kedatangan". Jalankan SELECT lagi dan perhatikan A001 / A004 menerima kedatangan yang sama untuk kedua kalinya — terhitung ganda.

UPDATE … FROM: gabungkan tabel lain dan perbarui

Menulis UPDATE table SET col = value FROM other_table WHERE join_condition memungkinkan kamu men-join target UPDATE dengan tabel yang disebut di FROM dan memperbarui sekali jalan, dengan langsung merujuk kolom sisi join di SET.

-- Contoh UPDATE … FROM: naikkan price 10 hanya untuk item yang punya kedatangan
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- Hanya sku yang ter-join (A001 / A004) yang price-nya naik
SELECT sku, price FROM stock ORDER BY sku;

Baris di stock yang tidak punya pasangan di sisi FROM (stock_in) gagal pada kondisi join di WHERE dan otomatis keluar dari target update.

Berbeda dengan bentuk correlated subquery, kamu tidak perlu berjaga-jaga terhadap NULL secara terpisah.

Bayangkan kebutuhannya: "lakukan refleksi stok yang sama dengan Latihan 1, tetapi tulis lebih ringkas dengan UPDATE … FROM."

① Tempatkan stock sebagai target update dan stock_in di sisi FROM, join pada sku, dan tulis UPDATE … FROM yang menambahkan add_qty ke qty.

② Akhiri skrip dengan SELECT sku, qty FROM stock ORDER BY sku; dan konfirmasi bahwa hanya sku yang ter-join (A001 / A004) yang stoknya naik.

DELETE dengan kondisi subquery, dan memeriksa jumlah baris yang terpengaruh

Kamu bisa menaruh subquery di WHERE dari DELETE, seperti DELETE FROM table WHERE col IN (SELECT ...) atau WHERE EXISTS (SELECT ...).

Ini memungkinkan penghapusan yang tidak bisa diekspresikan dengan nilai tetap: "hapus hanya baris yang ada / tidak ada di tabel lain," atau "hapus baris yang hasil agregatnya memenuhi suatu kondisi."

Penghapusan bersifat merusak dan tidak bisa diulang, jadi praktik standar di produksi adalah menjalankan dulu WHERE yang sama di dalam SELECT COUNT(*) untuk memeriksa jumlah baris yang terpengaruh, lalu menulis ulang menjadi DELETE.

Setelah eksekusi, verifikasi dengan SELECT COUNT(*) FROM table; untuk mengonfirmasi bahwa jumlah baris yang tersisa sesuai dengan yang kamu harapkan.

-- Periksa dulu apa yang akan dihapus: item di bawah harga rata-rata (stock belum berubah)
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- Setelah puas, ganti ke DELETE lalu periksa jumlah yang tersisa
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

Bayangkan kebutuhannya: "hapus dari stock kandidat penghentian — sku yang qty-nya 0 dan tidak ada kedatangan terjadwal di stock_in." Karena ini latihan terakhir artikel, kamu akan melakukan penghapusan destruktif terhadap tabel hidup.

① Tulis DELETE dengan kondisi subquery yang menghapus baris dari stock di mana qty adalah 0 dan sku tidak ada di stock_in.

② Setelah delete, jalankan SELECT COUNT(*) AS remaining FROM stock; untuk memastikan berapa baris yang tersisa. Kamu juga bisa memakai SELECT * FROM stock ORDER BY sku; untuk memverifikasi bahwa A005 (Glue / qty 0 / tidak ada kedatangan) sudah hilang.