UPSERT (ON CONFLICT) và các ứng dụng bulk INSERT

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

UPSERT (sự pha trộn của UPDATE và INSERT —

thao tác "cập nhật nếu tồn tại, chèn nếu không" trong một câu lệnh) được hiện thực qua cú pháp INSERT … ON CONFLICT(key) DO UPDATE.

Nếu dòng va chạm với khóa chính hoặc ràng buộc UNIQUE, phần cập nhật sẽ chạy; nếu không, nó được chèn nguyên trạng.

Trước khi vào bài tập, hãy kiểm tra đị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); để xem tên cột, kiểu, và khóa chính của stock (UPSERT giả định sku là khóa chính).

② Xem trước hai bảng với SELECT * FROM stock ORDER BY sku; và SELECT * FROM stock_in;.

ON CONFLICT DO UPDATE — cập nhật nếu tồn tại, chèn nếu không

Viết INSERT INTO table(...) VALUES (...) ON CONFLICT(key_col) DO UPDATE SET col = ... có nghĩa: nếu dòng bạn cố chèn va chạm với khóa được khai báo trong ON CONFLICT (khóa chính hoặc cột UNIQUE), phần cập nhật DO UPDATE sẽ chạy; nếu không có va chạm, dòng được chèn bình thường.

Bên trong DO UPDATE, một tên bảng đặc biệt excluded cho phép bạn tham chiếu "các giá trị của dòng mà bạn đã cố chèn".

Viết qty = qty + excluded.qty và, khi va chạm, qty hiện có sẽ được cộng thêm qty mà bạn cố chèn.

Dùng qty = excluded.qty để ghi đè và qty = qty + excluded.qty để cộng dồn, tùy theo nhu cầu.

-- Va chạm với sku hiện có → DO UPDATE cộng vào qty
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- sku mới → không va chạm, chỉ được chèn
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');

Hãy hình dung yêu cầu: "trong xử lý nhập kho, cộng vào tồn kho cho các sku hiện có, và đăng ký mới cho các sku chưa biết." (Chạy đúng thì phần giải thích sẽ hiện ra.)

① Trước UPSERT, chạy SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku; để xác nhận A001 đã tồn tại còn A006 thì chưa.

② Với A001 đã có, UPSERT với name Pen, qty 15, price 80. Khi va chạm, cộng giá trị chèn vào qty hiện có (không ghi đè).

③ Với A006 chưa biết, chạy cùng dạng UPSERT với name Stapler, qty 3, price 200 (không va chạm, nên được chèn).

④ Cuối cùng, chạy lại cùng SELECT và xác nhận A001 đã được cộng vào và A006 đã được chèn.

DO NOTHING — không làm gì khi va chạm

Khi bạn không muốn cập nhật cũng không muốn chèn — nghĩa là "âm thầm bỏ qua khi va chạm" — hãy dùng ON CONFLICT(key) DO NOTHING.

Không va chạm thì dòng được chèn; khi va chạm thì dòng bị bỏ qua mà không báo lỗi.

Trong ví dụ dưới, cố chèn A002 đã tồn tại với DO NOTHING sẽ va chạm và bị bỏ qua, còn A007 mới sẽ được chèn.

-- Bỏ qua khi va chạm (DO NOTHING)
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 giữ nguyên giá trị ban đầu (qty 60 / price 250)
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

Hãy hình dung yêu cầu: "khi nạp nhiều dòng vào master stock, đừng bao giờ ghi đè các sku hiện có và chỉ thêm các sku chưa biết."

① Trước UPSERT, chạy SELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku; để xác nhận A003 tồn tại còn A007 thì chưa.

② Cố chèn A003 hiện có với name Clip, qty 0, price 0, dùng DO NOTHING để bị bỏ qua khi va chạm.

③ Thêm A007 chưa biết với name Eraser, qty 50, price 90 dùng cùng UPSERT DO NOTHING.

④ Cuối cùng, chạy lại cùng SELECT và xác nhận A003 không đổi còn A007 đã được thêm.

UPSERT nhiều dòng — kết hợp bulk INSERT với ON CONFLICT

Khi bạn gắn ON CONFLICT vào một INSERT nhiều dòng — VALUES (...),(...),(...) ngăn cách bằng dấu phẩy — mỗi dòng độc lập "cập nhật khi va chạm, chèn nếu không".

Bạn có thể phản ánh dữ liệu nhập kho trong một câu lệnh, thay thế vòng lặp thủ tục rẽ nhánh giữa UPDATE và INSERT cho mỗi dòng bằng một câu SQL duy nhất.

Ngay cả trong UPSERT nhiều dòng, excluded vẫn có nghĩa là "giá trị mà bạn đã cố chèn cho dòng này", nên viết qty = qty + excluded.qty sẽ áp dụng "cộng vào với các dòng hiện có, chèn nguyên trạng với các dòng mới" theo từng dòng.

Bài tập cuối của bài viết này là UPSERT hàng loạt cả 3 dòng của stock_in (A001 / A004 / A006).

-- UPSERT nhiều dòng: các dòng đã có cộng dồn qty và làm mới price; dòng mới được chèn
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;

Hãy hình dung yêu cầu: "phản ánh cả 3 dòng của bảng nhập kho stock_in vào stock trong một câu lệnh duy nhất." Đây là bài tập cuối của bài viết.

① Trước UPSERT, chạy SELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku; để xác nhận A001 / A004 tồn tại và A006 thì chưa.

② Trên stock, chèn 3 dòng trong một INSERT — A001 (name Pen / qty 50 / price 80), A004 (name Tape / qty 100 / price 150), A006 (name Marker / qty 30 / price 120) — và viết một UPSERT hàng loạt với ON CONFLICT(sku) DO UPDATE cộng vào qty khi va chạm.

③ Cuối cùng, đặt SELECT sku, name, qty FROM stock ORDER BY sku; và xác nhận A001 / A004 đã được cộng vào còn A006 đã được thêm mới.