Bài 26: UPDATE, DELETE và xóa mềm

Cô Mai cần cộng xu cho một học viên và đưa một bạn đã nghỉ ra khỏi danh sách đang học. Nếu sửa nhầm cả bảng, số dư của nhiều người sẽ thay đổi. Nếu xóa hồ sơ quá sớm, câu lạc bộ có thể mất thông tin cần xem lại sau này.

Điều kiện chọn dòng quyết định phạm vi sửa

Cập nhật (update) là thay giá trị trong những dòng đã có. Trong câu UPDATE, tên bảng cho biết nơi sửa, SET chỉ giá trị mới, còn WHERE chọn các dòng nhận thay đổi. Thiếu điều kiện lọc có thể khiến mọi dòng trong bảng bị sửa.

Trước hết, hãy xác định đối tượng bằng mã học viên. Tên người có thể trùng, còn khóa chính id phân biệt từng hồ sơ. Đừng dùng tên thay mã chỉ vì dữ liệu mẫu chưa có hai người trùng tên.

Cô Mai sửa người hướng dẫn của học viên mã 2. Truy vấn đọc trước thao tác giúp bạn kiểm phạm vi:

SELECT id, name, mentor
FROM students
WHERE id = 2;

Kết quả chỉ có Bình, hiện chưa có người hướng dẫn. Ta dùng đúng điều kiện ấy để phân công Cô Mai:

UPDATE students
SET mentor = 'Cô Mai'
WHERE id = 2;

Ô mentor của mã 2 thay từ NULL thành tên mới. Những người khác vẫn giữ thông tin cũ, vì không thỏa điều kiện. Phần SET mô tả cần sửa gì, nhưng chính WHERE mới giới hạn sửa cho ai.

Một điều kiện viết sai có thể ảnh hưởng không dòng nào mà không báo lỗi. Ví dụ, mã học viên không tồn tại khiến câu cập nhật không tìm được đối tượng. Vì thế, ứng dụng cần kiểm số dòng ảnh hưởng thay vì coi mọi lần chạy không lỗi là thành công.

Trong SQLite, hàm changes() trả số dòng bị tác động bởi câu thêm, sửa hoặc xóa gần nhất trên kết nối. Hãy thử phân công người hướng dẫn cho mã 99, hiện chưa có trong bảng:

UPDATE students
SET mentor = 'Cô Mai'
WHERE id = 99;
SELECT changes() AS affected;

Kết quả có affected bằng 0, và mọi hồ sơ giữ nguyên. Câu lệnh hợp lệ về cú pháp nhưng không tìm thấy dòng thỏa điều kiện. Khi cần sửa đúng một hồ sơ, kết quả này cho biết bạn phải kiểm lại mã.

• Trước khi sửa, đọc các dòng với cùng điều kiện.
• Sau khi sửa, kiểm cả giá trị mới và số dòng bị tác động.
• Nếu số dòng khác dự kiến, dừng quy trình và xác minh mã.

Cộng thêm khác với thay bằng một số đã tính sẵn

Nếu yêu cầu là tăng xu, giá trị mới phụ thuộc số đang lưu. Cập nhật tương đối (relative update) dùng giá trị hiện tại trong biểu thức tính. Gán một hằng số chỉ phù hợp khi yêu cầu thực sự là đặt số dư bằng hằng số đó.

Học viên mã 3 đang có 180 xu. Thầy Nam cộng thêm 5 xu cho bạn ấy:

UPDATE students
SET coins = coins + 5
WHERE id = 3;

Kết quả của Chi là 185 xu. Phần bên phải coins + 5 đọc số cũ, tính thêm rồi gán lại vào cột. Nếu số ban đầu thay đổi, câu lệnh vẫn đáp ứng yêu cầu tăng thêm cùng một lượng.

Ngược lại, SET coins = 185 chỉ cho đúng kết quả khi số cũ tình cờ là 180. Nếu học viên đã có 200 xu, gán như vậy còn làm số dư giảm. Một lời giải dùng số tính sẵn từ bộ mẫu không diễn tả đúng thao tác cộng.

Thiếu WHERE cũng là lỗi khác, dù biểu thức cộng đã đúng. Hãy xem kết quả khi điều kiện bị bỏ:

UPDATE students
SET coins = coins + 5;

Cả sáu dòng đều đổi số dư. SQLite không biết yêu cầu chỉ thưởng cho một người, nên vẫn thực hiện đúng câu bạn gửi. Muốn sửa lỗi này, cần bổ sung điều kiện đúng đối tượng, không cần đổi phép cộng.

Xóa dòng có thể bị quan hệ hiện tại ngăn lại

Xóa hẳn (hard delete) là bỏ dòng khỏi bảng bằng DELETE FROM. Điều kiện WHERE chọn dòng bị xóa, giống vai trò giới hạn phạm vi ở câu cập nhật. Lệnh xóa không có điều kiện sẽ nhắm toàn bộ bảng.

Trong dữ liệu mẫu, học viên mã 6 chưa có đăng ký, điểm hoặc lịch sử xu. Thao tác xóa hồ sơ này có thể hoàn tất mà không làm đứt tham chiếu:

DELETE FROM students
WHERE id = 6;

Bảng sau thao tác không còn Lan. Các học viên khác giữ nguyên vì mã của họ khác 6. Đây là kết quả trên dữ liệu mẫu, không phải quy tắc rằng học viên mã ấy luôn có thể bị xóa ở mọi nơi.

Học viên mã 1 lại có nhiều dòng được tham chiếu. Với lược đồ hiện tại không khai báo xóa lan truyền, thao tác sau bị chặn:

DELETE FROM students
WHERE id = 1;

An vẫn còn trong bảng sau lỗi. Ràng buộc bảo vệ các dòng đang trỏ tới hồ sơ, thay vì tự xóa chúng. Xóa các dòng liên quan chỉ để hết lỗi có thể làm mất lịch sử học tập hoặc giao dịch.

Khi gặp lỗi này, hãy quay về yêu cầu: có cần xóa dữ liệu hay chỉ cần ngừng hiển thị người đó trong danh sách đang hoạt động? Hai nhu cầu dẫn tới hai cách xử lý khác nhau. Đừng tắt khóa ngoại để biến một quyết định chưa rõ thành thao tác xóa thành công.

Lưu trữ hồ sơ bằng trạng thái thay cho xóa

Xóa mềm (soft delete) giữ dòng trong bảng nhưng đánh dấu để danh sách hoạt động loại nó ra. Ở đây, ta thêm cột archived, nhận 0 khi đang hoạt động và 1 khi đã lưu trữ. Ràng buộc chỉ cho phép hai trạng thái này.

Ví dụ này dùng lược đồ bổ sung cột archived cho students. Giá trị mặc định 0 áp dụng cho các hồ sơ đang có. Phần thay đổi cấu trúc được khai báo riêng để bảng đầu vào hiển thị đủ cột mới:

Ta đánh dấu học viên mã 4 đã lưu trữ và xem danh sách hoạt động:

UPDATE students
SET archived = 1
WHERE id = 4;
SELECT id, name
FROM students
WHERE archived = 0
ORDER BY id;

Dung có archived bằng 1 trong bảng sau sửa, nhưng không có trong kết quả đọc. Các đăng ký và điểm của bạn ấy vẫn giữ tham chiếu hợp lệ. Để xem lại lịch sử, truy vấn khác có thể chọn cả hồ sơ đã lưu trữ.

Xóa mềm không tự thay mọi truy vấn của ứng dụng. Nếu danh sách hoạt động quên điều kiện, người đã lưu trữ vẫn xuất hiện. Vì thế, cần áp dụng cùng quy tắc ở danh sách, báo cáo và các thao tác dành cho người đang học.

Khi cần khôi phục một hồ sơ đã lưu trữ, bạn có thể đưa trạng thái về giá trị đang hoạt động. Tuy nhiên, hãy kiểm điều kiện tiếp nhận trước khi làm. Giữ dòng giúp bảo toàn quan hệ, nhưng không có nghĩa mọi đăng ký cũ đều nên được tiếp tục ngay.

Lỗi thường gặp

Tóm tắt

• SET chọn nội dung sửa, còn WHERE chọn các dòng nhận thay đổi.
• Cộng vào giá trị hiện tại giúp diễn tả đúng yêu cầu tăng số dư.
• DELETE có thể bị khóa ngoại chặn khi hồ sơ còn được tham chiếu.
• Xóa mềm giữ dòng và lịch sử, đồng thời đòi bộ lọc trong các truy vấn hoạt động.

Kiểm tra nhanh

Đọc thêm

• SQLite: cú pháp và tác động của UPDATE.
• SQLite: xóa dòng bằng DELETE.