Câu lệnh UPDATE trong MySQL

Câu lệnh UPDATE trong MySQL được sử dụng để cập nhật dữ liệu đã tồn tại trong bảng. Hướng dẫn này sẽ bao gồm tất cả các trường hợp, từ ví dụ cơ bản đến nâng cao, với giải thích chi tiết và minh họa đầy đủ.

Giới thiệu câu lệnh UPDATE trong MySQL

1. Cấu trúc cơ bản của câu lệnh UPDATE

Cú pháp cơ bản của câu lệnh UPDATE trong MySQL là:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Trong đó:

• table_name: Tên bảng mà bạn muốn cập nhật dữ liệu.
• column1, column2, ...: Các cột cần cập nhật.
• value1, value2, ...: Giá trị mới mà bạn muốn cập nhật cho các cột tương ứng.
• condition: Điều kiện lọc để chỉ định các dòng nào sẽ được cập nhật.

Nếu bạn không chỉ định điều kiện WHERE, MySQL sẽ cập nhật tất cả các dòng trong bảng.

Ví dụ cơ bản:

Giả sử bạn có bảng users với các cột id, name, và email. Để cập nhật địa chỉ email của một người dùng có id = 1, bạn có thể sử dụng câu lệnh sau:

UPDATE users
SET email = '[email protected]'
WHERE id = 1;

Câu lệnh trên sẽ cập nhật giá trị email thành '[email protected]' cho dòng có id = 1.

2. Cập nhật nhiều cột

Bạn có thể cập nhật nhiều cột cùng lúc bằng cách liệt kê chúng trong câu lệnh SET.

Ví dụ:

Giả sử bạn muốn cập nhật cả tên và email của người dùng có id = 2:

UPDATE users
SET name = 'Jane Smith', email = '[email protected]'
WHERE id = 2;

Câu lệnh trên sẽ cập nhật cả cột name và email cho dòng có id = 2.

3. Cập nhật tất cả các dòng trong bảng

Nếu bạn không sử dụng WHERE, tất cả các dòng trong bảng sẽ bị cập nhật.

Ví dụ:

Nếu bạn muốn đặt tất cả địa chỉ email trong bảng users thành '[email protected]':

UPDATE users
SET email = '[email protected]';

Câu lệnh trên sẽ cập nhật giá trị của cột email cho tất cả các dòng trong bảng users.

4. Cập nhật dựa trên điều kiện

Bạn có thể sử dụng WHERE để chỉ định một hoặc nhiều điều kiện cho việc cập nhật dữ liệu.

Ví dụ:

Nếu bạn muốn cập nhật tên của tất cả người dùng có email thuộc miền @example.com:

UPDATE users
SET name = 'Anonymous'
WHERE email LIKE '%@example.com';

Câu lệnh trên sẽ cập nhật cột name cho tất cả các dòng có email kết thúc bằng @example.com.

5. Cập nhật với phép tính

MySQL cho phép bạn sử dụng các phép toán để cập nhật giá trị của cột.

Ví dụ:

Giả sử bạn có bảng inventory với các cột product_id, stock (số lượng tồn kho). Bạn muốn tăng số lượng tồn kho của sản phẩm có product_id = 101 thêm 10:

UPDATE inventory
SET stock = stock + 10
WHERE product_id = 101;

Câu lệnh trên sẽ cộng thêm 10 vào giá trị hiện tại của cột stock cho sản phẩm có product_id = 101.

6. Cập nhật nhiều dòng với điều kiện khác nhau

Bạn có thể sử dụng hàm CASE để cập nhật giá trị khác nhau dựa trên các điều kiện khác nhau cho các dòng khác nhau trong cùng một câu lệnh UPDATE.

Ví dụ:

Giả sử bạn muốn cập nhật bảng users sao cho người dùng có id = 1 có tên mới là ‘Alice’, và người dùng có id = 2 có tên mới là ‘Bob’:

UPDATE users
SET name = CASE 
    WHEN id = 1 THEN 'Alice'
    WHEN id = 2 THEN 'Bob'
    ELSE name
END;

Câu lệnh trên sẽ cập nhật cột name cho dòng có id = 1 thành ‘Alice’ và cho dòng có id = 2 thành ‘Bob’. Các dòng khác sẽ giữ nguyên giá trị của cột name.

7. Cập nhật với khóa ngoại

Nếu bảng của bạn có ràng buộc khóa ngoại (FOREIGN KEY), khi cập nhật các cột liên quan đến khóa ngoại, bạn phải đảm bảo rằng giá trị mới vẫn tuân thủ ràng buộc khóa ngoại, tức là nó phải tồn tại trong bảng liên quan.

Ví dụ:

Giả sử bạn có bảng orders với cột customer_id là khóa ngoại tham chiếu tới bảng customers. Để cập nhật customer_id, giá trị mới phải tồn tại trong bảng customers.

UPDATE orders
SET customer_id = 5
WHERE order_id = 101;

Nếu customer_id = 5 không tồn tại trong bảng customers, câu lệnh này sẽ trả về lỗi.

8. Cập nhật với dữ liệu từ bảng khác

Bạn có thể cập nhật dữ liệu trong một bảng dựa trên dữ liệu từ bảng khác thông qua việc kết hợp (JOIN) hai bảng trong câu lệnh UPDATE.

Ví dụ:

Giả sử bạn có bảng products và suppliers, và bạn muốn cập nhật tên nhà cung cấp (supplier_name) trong bảng products dựa trên dữ liệu từ bảng suppliers:

UPDATE products p
JOIN suppliers s ON p.supplier_id = s.supplier_id
SET p.supplier_name = s.name
WHERE p.product_id = 101;

Câu lệnh trên sẽ cập nhật tên nhà cung cấp trong bảng products dựa trên giá trị name từ bảng suppliers cho sản phẩm có product_id = 101.

9. Cập nhật với LIMIT

MySQL hỗ trợ từ khóa LIMIT trong câu lệnh UPDATE để giới hạn số lượng dòng được cập nhật.

Ví dụ:

Nếu bạn muốn chỉ cập nhật 5 dòng đầu tiên trong bảng orders có status = 'pending':

UPDATE orders
SET status = 'processed'
WHERE status = 'pending'
LIMIT 5;

Câu lệnh trên sẽ chỉ cập nhật 5 dòng đầu tiên có status = 'pending' thành 'processed'.

10. Cập nhật với ORDER BY

MySQL cho phép bạn kết hợp ORDER BY với UPDATE để sắp xếp các dòng được cập nhật theo một thứ tự nhất định.

Ví dụ:

Giả sử bạn muốn cập nhật các dòng có status = 'pending' theo thứ tự thời gian từ cũ nhất đến mới nhất:

UPDATE orders
SET status = 'processed'
WHERE status = 'pending'
ORDER BY order_date ASC
LIMIT 5;

Câu lệnh trên sẽ cập nhật 5 dòng đầu tiên có status = 'pending' theo thứ tự ngày đặt hàng từ cũ nhất.

11. Cập nhật và sử dụng giá trị mặc định (DEFAULT)

Bạn có thể sử dụng từ khóa DEFAULT để cập nhật một cột về giá trị mặc định của nó.

Ví dụ:

Giả sử bảng products có cột price với giá trị mặc định là 100, bạn có thể đặt lại giá trị của cột này về giá trị mặc định:

UPDATE products
SET price = DEFAULT
WHERE product_id = 101;

Câu lệnh trên sẽ cập nhật cột price của sản phẩm có product_id = 101 về giá trị mặc định là 100.

12. Sử dụng UPDATE IGNORE

Nếu bạn muốn MySQL bỏ qua các lỗi (ví dụ, vi phạm khóa ngoại hoặc khóa duy nhất) trong khi cập nhật, bạn có thể sử dụng UPDATE IGNORE.

Ví dụ:

Giả sử bạn có bảng users với cột email là duy nhất. Bạn có thể sử dụng UPDATE IGNORE để bỏ qua lỗi khi cập nhật nếu có vi phạm:

UPDATE IGNORE users
SET email = '[email protected]'
WHERE id = 10;

Nếu có dòng nào vi phạm ràng buộc duy nhất trên email, câu lệnh sẽ bỏ qua dòng đó và tiếp tục thực hiện các cập nhật khác mà không báo lỗi.

13. Cập nhật với dữ liệu nhị phân (BLOB)

Trong MySQL, bạn có thể cập nhật dữ liệu nhị phân trong các cột kiểu BLOB hoặc TEXT.

Ví dụ:

Giả sử bạn có bảng files với cột content lưu trữ dữ liệu nhị phân, bạn có thể cập nhật nội dung tệp như sau:

UPDATE files
SET content = LOAD_FILE('/path/to/new_file.jpg')
WHERE file_id = 1;

Câu lệnh trên sẽ cập nhật nội dung của tệp mới vào bảng files.

14. Tối ưu hóa và thực hiện hiệu quả câu lệnh UPDATE

• Sử dụng chỉ mục (Indexes): Đảm bảo rằng các cột trong điều kiện WHERE được lập chỉ mục để tăng hiệu suất.
• Giới hạn số lượng dòng được cập nhật: Sử dụng LIMIT nếu bạn chỉ cần cập nhật một số lượng dòng nhỏ.
• Sử dụng EXPLAIN: Để kiểm tra cách MySQL thực hiện câu lệnh UPDATE, bạn có thể sử dụng EXPLAIN trước câu lệnh.

Ví dụ:

EXPLAIN UPDATE users SET email = '[email protected]' WHERE id = 1;

Ví dụ nâng cao về câu lệnh UPDATE

Dưới đây là các ví dụ nâng cao chuyên sâu hơn về câu lệnh UPDATE trong MySQL, bao gồm tất cả các trường hợp chi tiết và phức tạp hơn, từ việc sử dụng JOIN, WHERE, đến việc cập nhật đồng thời nhiều dòng với các điều kiện phức tạp, hoặc thậm chí kết hợp với các hàm và truy vấn con.

1. Cập nhật với JOIN trên nhiều bảng

Bạn có thể thực hiện cập nhật trên một bảng nhưng dựa vào dữ liệu của nhiều bảng khác bằng cách sử dụng JOIN.

Ví dụ:

Giả sử bạn có bảng employees và bảng departments, và bạn muốn cập nhật phòng ban (department_name) cho nhân viên dựa trên department_id từ bảng departments.

UPDATE employees e
JOIN departments d ON e.department_id = d.department_id
SET e.department_name = d.name
WHERE e.employee_id = 123;

Trong ví dụ này, bạn cập nhật tên phòng ban (department_name) cho nhân viên có employee_id = 123 từ bảng departments dựa trên giá trị department_id trong cả hai bảng.

2. Cập nhật với truy vấn con (Subquery)

Sử dụng một truy vấn con (subquery) trong phần SET hoặc WHERE của câu lệnh UPDATE để cập nhật dữ liệu dựa trên kết quả của một truy vấn khác.

Ví dụ:

Giả sử bạn muốn cập nhật lương (salary) của nhân viên bằng với mức lương cao nhất trong cùng một phòng ban (department_id).

UPDATE employees e
SET e.salary = (
    SELECT MAX(salary)
    FROM employees
    WHERE department_id = e.department_id
)
WHERE e.department_id = 5;

Câu lệnh trên sẽ cập nhật lương của tất cả nhân viên trong phòng ban có department_id = 5 với mức lương cao nhất của phòng ban đó.

3. Cập nhật dựa trên điều kiện phức tạp với CASE

Câu lệnh CASE giúp bạn cập nhật nhiều giá trị khác nhau cho cùng một cột, dựa trên các điều kiện khác nhau.

Ví dụ:

Giả sử bạn muốn cập nhật mức lương dựa trên cấp bậc của nhân viên (position):

UPDATE employees
SET salary = CASE
    WHEN position = 'Manager' THEN salary * 1.10
    WHEN position = 'Developer' THEN salary * 1.05
    WHEN position = 'Intern' THEN salary * 1.02
    ELSE salary
END;

Câu lệnh trên sẽ tăng lương cho nhân viên dựa trên cấp bậc, với mức tăng khác nhau tùy thuộc vào chức danh.

4. Cập nhật với khóa ngoại (FOREIGN KEY) và tính toàn vẹn dữ liệu

Khi làm việc với các bảng có ràng buộc khóa ngoại, bạn cần đặc biệt chú ý đến việc cập nhật các cột liên quan đến khóa ngoại. Để tránh vi phạm ràng buộc, bạn có thể sử dụng ON UPDATE CASCADE.

Ví dụ:

Giả sử bạn có hai bảng orders và customers, với customer_id là khóa ngoại trong bảng orders. Khi bạn cập nhật customer_id trong bảng customers, các bản ghi tương ứng trong bảng orders cũng sẽ được cập nhật tự động.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON UPDATE CASCADE
);

Sau đó, khi bạn cập nhật customer_id trong bảng customers:

UPDATE customers
SET customer_id = 10
WHERE customer_id = 5;

Câu lệnh này sẽ tự động cập nhật customer_id trong bảng orders cho các đơn hàng liên quan mà không cần phải làm thủ công.

5. Cập nhật đồng thời nhiều bảng với UPDATE và JOIN

Bạn có thể cập nhật nhiều bảng cùng một lúc bằng cách sử dụng JOIN để liên kết các bảng và thực hiện cập nhật trên cả hai.

Ví dụ:

Giả sử bạn muốn cập nhật đồng thời bảng employees và bảng salaries, dựa trên điều kiện của department_id.

UPDATE employees e
JOIN salaries s ON e.employee_id = s.employee_id
SET e.department_name = 'Marketing', s.salary = s.salary * 1.10
WHERE e.department_id = 3;

Câu lệnh trên sẽ cập nhật tên phòng ban trong bảng employees và mức lương trong bảng salaries cho tất cả các nhân viên thuộc department_id = 3.

6. Cập nhật với LIMIT và ORDER BY

MySQL hỗ trợ cập nhật dữ liệu có giới hạn số lượng dòng và sắp xếp thứ tự của các dòng cần cập nhật bằng cách sử dụng LIMIT và ORDER BY.

Ví dụ:

Bạn có thể muốn cập nhật 10 nhân viên có lương thấp nhất trong công ty, và tăng lương của họ.

UPDATE employees
SET salary = salary + 500
WHERE department_id = 2
ORDER BY salary ASC
LIMIT 10;

Câu lệnh trên sẽ chọn ra 10 nhân viên có lương thấp nhất trong phòng ban department_id = 2 và tăng lương cho họ thêm 500.

7. Cập nhật dữ liệu trong cột có kiểu dữ liệu JSON

Kể từ MySQL 5.7, bạn có thể lưu trữ và cập nhật dữ liệu JSON trong các bảng của mình. Cập nhật dữ liệu JSON đòi hỏi phải sử dụng các hàm đặc biệt như JSON_SET, JSON_REPLACE, hoặc JSON_REMOVE.

Ví dụ:

Giả sử bạn có bảng employees với cột profile lưu trữ dữ liệu JSON, và bạn muốn cập nhật địa chỉ của một nhân viên cụ thể.

UPDATE employees
SET profile = JSON_SET(profile, '$.address', '123 New St')
WHERE employee_id = 1;

Câu lệnh trên sẽ cập nhật trường address trong dữ liệu JSON của nhân viên có employee_id = 1.

8. Cập nhật dữ liệu có kiểu BLOB hoặc TEXT

MySQL hỗ trợ cập nhật dữ liệu nhị phân hoặc dữ liệu lớn kiểu BLOB và TEXT. Đây là các kiểu dữ liệu thường dùng để lưu trữ các file hoặc chuỗi văn bản dài.

Ví dụ:

Giả sử bạn có bảng documents với cột file_content kiểu BLOB. Để cập nhật nội dung của một file từ hệ thống tập tin, bạn có thể sử dụng hàm LOAD_FILE.

UPDATE documents
SET file_content = LOAD_FILE('/path/to/file.pdf')
WHERE document_id = 101;

Câu lệnh trên sẽ cập nhật nội dung file vào cột file_content cho tài liệu có document_id = 101.

9. Cập nhật với khóa tự động tăng (AUTO_INCREMENT)

Trực tiếp cập nhật giá trị của cột có kiểu AUTO_INCREMENT là không được phép trong MySQL. Tuy nhiên, bạn có thể điều chỉnh giá trị tự động tăng bằng cách sử dụng truy vấn ALTER TABLE.

Ví dụ:

Giả sử bạn có bảng products với cột product_id là AUTO_INCREMENT, và bạn muốn điều chỉnh giá trị tiếp theo của cột này.

ALTER TABLE products AUTO_INCREMENT = 200;

Sau khi thực hiện câu lệnh này, giá trị tiếp theo của product_id sẽ là 200.

10. Cập nhật đồng thời với Trigger

Bạn có thể sử dụng Trigger để tự động cập nhật một bảng khi có sự thay đổi trong bảng khác. Ví dụ, khi một bản ghi trong bảng orders được cập nhật, Trigger có thể tự động cập nhật trạng thái của bảng inventory.

Ví dụ:

Tạo Trigger để tự động cập nhật tồn kho khi một đơn hàng được thực hiện.

CREATE TRIGGER update_inventory
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    UPDATE inventory
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
END;

Trigger này sẽ tự động trừ số lượng hàng tồn kho mỗi khi có cập nhật đơn hàng mới.

11. Cập nhật dữ liệu với khóa chính phức tạp (Composite Primary Key)

Trong một số trường hợp, bảng của bạn có thể có khóa chính là tổ hợp của nhiều cột. Khi đó, việc cập nhật dữ liệu yêu cầu bạn chỉ định cả hai cột của khóa chính.

Ví dụ:

Giả sử bạn có bảng project_assignments với khóa chính là tổ hợp của hai cột employee_id và project_id. Bạn có thể cập nhật trạng thái dự án cho một nhân viên như sau:

UPDATE project_assignments
SET status = 'Completed'
WHERE employee_id = 1 AND project_id = 5;

Câu lệnh này sẽ cập nhật trạng thái dự án cho bản ghi với tổ hợp khóa chính employee_id = 1 và project_id = 5.

12. Cập nhật dữ liệu sử dụng các hàm thời gian

Bạn có thể sử dụng các hàm thời gian như NOW(), DATE_ADD, và DATE_SUB để cập nhật các cột kiểu thời gian.

Ví dụ:

Cập nhật cột last_login cho tất cả người dùng đăng nhập trong tuần trước.

UPDATE users
SET last_login = NOW()
WHERE last_login BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW();

Câu lệnh này cập nhật thời gian đăng nhập cuối cùng cho những người dùng đã đăng nhập trong vòng 7 ngày qua.

Trên đây là tổng hợp tất cả các trường hợp nâng cao của câu lệnh UPDATE trong MySQL, giúp bạn làm việc với dữ liệu một cách linh hoạt và hiệu quả trong nhiều tình huống phức tạp.

• Câu Lệnh Trong MySQL
• MySQL
• UPDATE