Cách bulk update nhiều hàng một câu lệnh MySQL (CASE WHEN)

Cú pháp

Sử dụng CASE trong mệnh đề SET của UPDATE để cập nhật nhiều hàng với giá trị khác nhau:

UPDATE ten_bang 
SET ten_cot_cap_nhat = CASE ten_cot_dieu_kien 
    WHEN gia_tri_1 THEN 'gia_tri_moi_1' 
    WHEN gia_tri_2 THEN 'gia_tri_moi_2' 
    ...
    ELSE ten_cot_cap_nhat  -- Giữ nguyên nếu không khớp
END 
WHERE ten_cot_dieu_kien IN (gia_tri_1, gia_tri_2, ...);

Ví dụ: Cập nhật tên sách theo ID

1. Tạo bảng

mysql> CREATE TABLE UpdateAllDemo ( 
-> BookId INT, 
-> BookName VARCHAR(200) 
-> ); 
Query OK, 0 rows affected (1.18 sec)

2. Chèn dữ liệu

mysql> INSERT INTO UpdateAllDemo VALUES(1000,'Introduction to C'); 
Query OK, 1 row affected (0.15 sec) 
mysql> INSERT INTO UpdateAllDemo VALUES(1001,'Introduction to Java'); 
Query OK, 1 row affected (0.21 sec)

3. Dữ liệu gốc

mysql> SELECT * FROM UpdateAllDemo;

+--------+----------------------+ 
| BookId | BookName             | 
+--------+----------------------+ 
| 1000   | Introduction to C    | 
| 1001   | Introduction to Java | 
+--------+----------------------+ 
2 rows in set (0.00 sec)

4. Bulk UPDATE với CASE

mysql> UPDATE UpdateAllDemo 
-> SET BookName = CASE BookId 
->     WHEN 1000 THEN 'C in Depth' 
->     WHEN 1001 THEN 'Java in Depth' 
->     END 
-> WHERE BookId IN (1000, 1001); 
Query OK, 2 rows affected (0.24 sec)

5. Kết quả

mysql> SELECT * FROM UpdateAllDemo;

+--------+---------------+ 
| BookId | BookName      | 
+--------+---------------+ 
| 1000   | C in Depth    | 
| 1001   | Java in Depth | 
+--------+---------------+ 
2 rows in set (0.00 sec)

Ưu điểm của bulk update

• 1 round-trip thay vì N round-trips
• Atomic: Thành công tất cả hoặc rollback tất cả
• Ít lock hơn: Khóa bảng 1 lần thay vì N lần
• Hiệu năng: Nhanh hơn gấp nhiều lần so với loop UPDATE

Mở rộng: Cập nhật nhiều cột cùng lúc

UPDATE products 
SET 
    price = CASE id 
        WHEN 1 THEN 100 
        WHEN 2 THEN 200 
        ELSE price 
    END,
    stock = CASE id 
        WHEN 1 THEN 50 
        WHEN 2 THEN 30 
        ELSE stock 
    END,
    status = CASE 
        WHEN id IN (1,2) THEN 'updated' 
        ELSE status 
    END
WHERE id IN (1, 2);

Cách xây dựng động (Dynamic SQL) cho nhiều bản ghi

-- Giả sử có danh sách (id, new_name) từ ứng dụng
-- Xây dựng query trong code (Java/Python/PHP):
-- UPDATE table SET name = CASE id WHEN 1 THEN 'a' WHEN 2 THEN 'b' END WHERE id IN (1,2)

Lưu ý quan trọng

• Luôn có ELSE clause (giữ nguyên giá trị) hoặc WHERE IN khớp chính xác
• Kiểm tra số lượng hàng ảnh hưởng: ROW_COUNT()
• Transaction: Bọc trong START TRANSACTION; ... COMMIT; nếu cần rollback
• Giới hạn: MySQL có giới hạn độ dài query (max_allowed_packet)

So sánh với INSERT ... ON DUPLICATE KEY UPDATE