Câu lệnh UPDATE JOIN trong MySQL

Trong hướng dẫn này, bạn sẽ học cách sử dụng câu lệnh UPDATE JOIN trong MySQL để thực hiện cập nhật bảng chéo. Chúng tôi sẽ hướng dẫn bạn từng bước cách sử dụng mệnh đề INNER JOIN và mệnh đề LEFT JOIN với câu lệnh UPDATE.

Cú pháp UPDATE JOIN trong MySQL

Bạn thường sử dụng join để truy vấn các hàng từ một bảng (trong trường hợp INNER JOIN) hoặc có thể không (trong trường hợp LEFT JOIN) các hàng khớp trong một bảng khác. Trong MySQL, bạn có thể sử dụng mệnh đề JOIN trong câu lệnh UPDATE để thực hiện cập nhật bảng chéo.

Cú pháp của MySQL UPDATE JOIN  như sau:

UPDATE T1, T2,
[INNER JOIN | LEFT JOIN] T1 ON T1.C1 = T2. C1
SET T1.C2 = T2.C2, 
    T2.C3 = expr
WHERE condition

Hãy xem xét cú pháp UPDATE JOIN trong MySQL chi tiết hơn:

• Đầu tiên, chỉ định bảng chính (T1) và bảng mà bạn muốn bảng chính tham gia (T2) sau mệnh đề UPDATE. Chú ý rằng bạn phải chỉ định ít nhất một bảng sau mệnh đề UPDATE. Dữ liệu trong bảng không được chỉ định sau mệnh đề UPDATE sẽ không được cập nhật.
• Tiếp theo, chỉ định một loại join mà bạn muốn sử dụng, tức là INNER JOIN  hoặc LEFT JOIN và một vị từ. Mệnh đề JOIN phải xuất hiện ngay sau mệnh đề UPDATE.
• Sau đó, gán giá trị mới cho các cột trong bảng T1 và / hoặc T2 mà bạn muốn cập nhật.
• Sau đó, chỉ định một điều kiện trong mệnh đề WHERE để giới hạn các hàng thành các hàng để cập nhật.

Nếu bạn làm theo hướng dẫn câu lệnh UPDATE, bạn sẽ nhận thấy rằng có một cách khác để cập nhật bảng chéo dữ liệu bằng cú pháp sau:

UPDATE T1, T2
SET T1.c2 = T2.c2,
      T2.c3 = expr
WHERE T1.c1 = T2.c1 AND condition

Câu lệnh UPDATE này hoạt động tương tự như UPDATE JOIN với một mệnh đề ngầm định INNER JOIN. Nó có nghĩa là bạn có thể viết lại câu lệnh trên như sau:

UPDATE T1,T2
INNER JOIN T2 ON T1.C1 = T2.C1
SET T1.C2 = T2.C2,
      T2.C3 = expr
WHERE condition

Hãy cùng xem một số ví dụ về việc sử dụng câu lệnh UPDATE JOIN để hiểu rõ hơn.

Ví dụ về UPDATE JOIN trong MySQL

Chúng ta sẽ sử dụng một cơ sở dữ liệu mẫu mới có tên empdb để minh họa. Cơ sở dữ liệu mẫu này bao gồm hai bảng:

• Bảng employees lưu trữ dữ liệu nhân viên với id, tên, hiệu suất và lương của nhân viên.
• Bảng merits lưu trữ hiệu suất của nhân viên và tỷ lệ phần trăm thành tích.

Các câu lệnh sau tạo và tải dữ liệu trong cơ sở dữ liệu mẫu empdb:

CREATE DATABASE IF NOT EXISTS empdb;

USE empdb;

-- create tables
CREATE TABLE merits (
    performance INT(11) NOT NULL,
    percentage FLOAT NOT NULL,
    PRIMARY KEY (performance)
);

CREATE TABLE employees (
    emp_id INT(11) NOT NULL AUTO_INCREMENT,
    emp_name VARCHAR(255) NOT NULL,
    performance INT(11) DEFAULT NULL,
    salary FLOAT DEFAULT NULL,
    PRIMARY KEY (emp_id),
    CONSTRAINT fk_performance FOREIGN KEY (performance)
        REFERENCES merits (performance)
);
-- insert data for merits table
INSERT INTO merits(performance,percentage)
VALUES(1,0),
      (2,0.01),
      (3,0.03),
      (4,0.05),
      (5,0.08);
-- insert data for employees table
INSERT INTO employees(emp_name,performance,salary)      
VALUES('Mary Doe', 1, 50000),
      ('Cindy Smith', 3, 65000),
      ('Sue Greenspan', 4, 75000),
      ('Grace Dell', 5, 125000),
      ('Nancy Johnson', 3, 85000),
      ('John Doe', 2, 45000),
      ('Lily Bush', 3, 55000);

Ví dụ về UPDATE JOIN với mệnh đề INNER JOIN trong MySQL

Giả sử bạn muốn điều chỉnh mức lương của nhân viên dựa trên kết quả hoạt động của họ.

Phần trăm thành tích được lưu trong bảng merits, do đó, bạn phải sử dụng câu lệnh UPDATE INNER JOIN để điều chỉnh lương của nhân viên trong bảng employees dựa trên số liệu percentage được lưu trong bảng merits.

Liên kết giữa các bảng employees và merit là trường performance. Xem truy vấn sau:

UPDATE employees
        INNER JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * percentage;

Đầu ra:

Cách hoạt động của truy vấn.

Chúng ta chỉ định bảng employees sau mệnh đề UPDATE vì chúng ta chỉ muốn cập nhật dữ liệu trong bảng employees.

Đối với mỗi hàng trong bảng employees, truy vấn kiểm tra giá trị trong cột hiệu suất so với giá trị trong cột hiệu suất trong bảng merits. Nếu nó tìm thấy một kết quả phù hợp, nó sẽ lấy percentage trong bảng merits và cập nhật cột salary trong bảng employees.

Bởi vì chúng ta bỏ qua mệnh đề WHERE trong câu lệnh UPDATE, tất cả các bản ghi trong bảng employees sẽ được cập nhật.

Ví dụ về UPDATE JOIN với LEFT JOIN trong MySQL

Giả sử công ty thuê thêm hai nhân viên:

INSERT INTO employees(emp_name,performance,salary)
VALUES('Jack William',NULL,43000),
      ('Ricky Bond',NULL,52000);

Bởi vì những nhân viên này là người mới được tuyển dụng nên dữ liệu hiệu suất của họ không có sẵn hoặc NULL.

Để tăng lương cho nhân viên mới, bạn không thể sử dụng câu lệnh UPDATE INNER JOIN vì dữ liệu hiệu suất của họ không có sẵn trong bảng merit. Đây là lý do tại sao UPDATE LEFT JOIN đến để giải cứu.

Về cơ bản câu lệnh UPDATE LEFT JOIN cập nhật một hàng trong bảng khi nó không có hàng tương ứng trong bảng khác.

Ví dụ: bạn có thể tăng lương cho một nhân viên mới lên 1,5% bằng cách sử dụng câu lệnh sau:

UPDATE employees
        LEFT JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    merits.percentage IS NULL;

Đầu ra:

Trong hướng dẫn này, chúng tôi đã chỉ cho bạn cách sử dụng UPDATE JOIN trong MySQL với các mệnh đề INNER JOIN và LEFT JOIN để thực hiện cập nhật bảng chéo.