UPDATE / DELETE với truy vấn con và JOIN

Dữ liệu sẽ dùng — stock và stock_in

Bài viết này đề cập một phong cách viết nâng cao hơn — cập nhật và xóa bằng giá trị từ bảng khác hoặc kết quả tổng hợp.

Cụ thể là ba mẫu: cập nhật mà giá trị được tính bằng truy vấn con tương quan (truy vấn con mà SELECT bên trong tham chiếu các cột của dòng bên ngoài, nên được đánh giá cho từng dòng), cập nhật kiểu kết hợp qua UPDATE … FROM với bảng khác, và DELETE có truy vấn con trong mệnh đề WHERE.

Trước khi vào các bài tập, hãy xem định nghĩa cột và dữ liệu mẫu của hai bảng — stock và stock_in.

① Chạy PRAGMA table_info(stock); để kiểm tra tên cột, kiểu và khóa chính của stock.

② Xem trước cả hai bảng với SELECT * FROM stock LIMIT 5; và SELECT * FROM stock_in;. Lưu ý rằng stock_in chứa một sku không tồn tại ở phía stock.

Dùng truy vấn con tương quan để cập nhật bằng giá trị từ bảng khác

Viết UPDATE table SET col = (SELECT ... WHERE subquery.key = table.key) sẽ đánh giá SELECT bên trong một lần cho mỗi dòng đích và ghi kết quả vào cột.

Vì SELECT bên trong tham chiếu một cột của đích UPDATE bên ngoài (stock.sku), nó được gọi là truy vấn con tương quan.

Điểm cần lưu ý: với các dòng stock không có dòng khớp trong stock_in, SELECT bên trong sẽ trả về NULL.

-- Không có COALESCE: qty bị ghi đè bằng NULL cho các sku không có khớp
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

Nếu bạn chạy qty = qty + (SELECT ...) như vậy, các dòng không có hàng nhập sẽ bị qty ghi đè thành NULL.

Để tránh điều này, hãy bọc truy vấn con bằng COALESCE(subquery, 0) để biến NULL thành 0, hoặc thu hẹp đích bằng WHERE EXISTS (...) để chỉ động đến những dòng thực sự có hàng nhập.

-- Xem trước giá trị sau cập nhật bằng SELECT (stock không bị thay đổi)
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- Khi ổn rồi, chuyển sang UPDATE (COALESCE chuyển NULL thành 0)
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

Hãy hình dung yêu cầu: "phản ánh số lượng trong bảng nhập kho stock_in vào qty của các dòng stock tương ứng." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Trước tiên chạy SELECT sku, qty FROM stock ORDER BY sku; để xem trạng thái trước khi cập nhật.

② Viết phiên bản COALESCE: một cập nhật truy vấn con tương quan cộng tổng stock_in.add_qty vào stock.qty. Với các sku không có dòng khớp trong stock_in, hãy coi NULL như 0 để giá trị của chúng không đổi. Chạy thêm SELECT sau đó để xác nhận.

③ Sau đó viết phiên bản EXISTS: cập nhật dùng WHERE EXISTS (...) để giới hạn đích chỉ ở "các dòng có hàng nhập". Chạy SELECT lại và để ý A001 / A004 nhận cùng lượng hàng nhập thêm một lần nữa — chúng bị cộng kép.

UPDATE … FROM: kết hợp với bảng khác và cập nhật

Viết UPDATE table SET col = value FROM other_table WHERE join_condition cho phép bạn kết hợp đích UPDATE với bảng được nêu trong FROM và cập nhật trong một bước, tham chiếu trực tiếp các cột phía kết hợp trong SET.

-- Ví dụ UPDATE … FROM: tăng price thêm 10 chỉ cho các mặt hàng có hàng nhập
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- Chỉ các sku đã kết hợp được (A001 / A004) thấy price tăng
SELECT sku, price FROM stock ORDER BY sku;

Các dòng trong stock không có dòng khớp ở phía FROM (stock_in) sẽ không thỏa điều kiện kết hợp trong WHERE và tự động bị loại khỏi đích cập nhật.

Khác với dạng truy vấn con tương quan, bạn không cần phải đề phòng NULL riêng.

Hãy hình dung yêu cầu: "làm cùng việc phản ánh tồn kho như Bài tập 1, nhưng viết gọn hơn bằng UPDATE … FROM."

① Đặt stock làm đích cập nhật và stock_in ở phía FROM, kết hợp trên sku, và viết một UPDATE … FROM cộng add_qty vào qty.

② Kết thúc script bằng SELECT sku, qty FROM stock ORDER BY sku; và xác nhận chỉ các sku đã kết hợp (A001 / A004) có tồn kho tăng.

DELETE với điều kiện truy vấn con, và kiểm tra số dòng bị ảnh hưởng

Bạn có thể đặt truy vấn con trong WHERE của DELETE, như DELETE FROM table WHERE col IN (SELECT ...) hoặc WHERE EXISTS (SELECT ...).

Nhờ vậy có thể xóa theo những điều kiện không thể diễn tả bằng giá trị cố định: "chỉ xóa các dòng tồn tại / không tồn tại ở bảng khác", hoặc "xóa các dòng có kết quả tổng hợp thỏa điều kiện".

Xóa là thao tác phá hủy và không thể hoàn tác, nên cách làm tiêu chuẩn trong sản xuất là chạy cùng WHERE trong SELECT COUNT(*) trước để kiểm tra số dòng bị ảnh hưởng, rồi mới đổi thành DELETE.

Sau khi thực thi, xác nhận bằng SELECT COUNT(*) FROM table; để đối chiếu số dòng còn lại có khớp với mong đợi không.

-- Xem trước cái sẽ bị xóa: các mặt hàng dưới giá trung bình (stock không bị thay đổi)
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- Khi ổn rồi, chuyển sang DELETE và kiểm tra số dòng còn lại
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

Hãy hình dung yêu cầu: "xóa khỏi stock các ứng viên ngừng kinh doanh — các sku có qty bằng 0 và không có hàng nhập sắp tới trong stock_in." Vì đây là bài tập cuối của bài viết, bạn sẽ thực hiện một xóa phá hủy trên bảng thật.

① Viết một DELETE với điều kiện truy vấn con loại bỏ các dòng khỏi stock mà qty bằng 0 và sku không tồn tại trong stock_in.

② Sau khi xóa, chạy SELECT COUNT(*) AS remaining FROM stock; để xác nhận còn lại bao nhiêu dòng. Bạn cũng có thể dùng SELECT * FROM stock ORDER BY sku; để xác nhận A005 (Glue / qty 0 / không có hàng nhập) đã biến mất.