全部教學 » MySQL update join語句

MySQL update join語句

2019-10-16 22:58:16

在本教學中，您將學習如何使用MySQL UPDATE JOIN語句來執行跨表更新。我們將逐步介紹如何使用INNER JOIN子句和LEFT JOIN子句與UPDATE語句一起使用。

1. MySQL UPDATE JOIN語法

我們經常使用join子句來查詢表中的行(在INNER JOIN的情況下)，或者可能沒有(在LEFT JOIN的情況下)另一個表中的相應行。 在MySQL中，可以在UPDATE語句中使用JOIN子句執行跨表更新。

MySQL UPDATE JOIN的語法如下：

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

讓我們更詳細地看看MySQL UPDATE JOIN語法：

  • 首先，在UPDATE子句之後，指定主表(T1)和希望主表連線表(T2)。 請注意，必須在UPDATE子句之後至少指定一個表。UPDATE子句後未指定的表中的資料未更新。
  • 第二，指定一種要使用的連線，即INNER JOIN或LEFT JOIN和連線條件。JOIN子句必須出現在UPDATE子句之後。
  • 第三，要為要更新的T1和/或T2表中的列分配新值。
  • 第四，WHERE子句中的條件用於指定要更新的行。

如果您學習過了UPDATE語句教學，您可能會注意到使用以下語法更新資料交叉表的另一種方法：

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

在這個UPDATE語句與具有隱式INNER JOIN子句的UPDATE JOIN工作相同。這意味著可以如下重寫上述語句：

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

讓我們來看一些使用UPDATE JOIN語句來更好地理解的例子。

2. MySQL UPDATE JOIN範例

我們將在這些例子中使用一個新的範例資料庫(empdb)。範例資料庫包含2個表：

  • employees表將儲存在員工編號，姓名，工作表現和工資的資料。
  • merits表儲存員工績效和績效百分比。

以下語句在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 Minsu', 3, 65000),
      ('Sue Greenspan', 4, 75000),
      ('Grace Dell', 5, 125000),
      ('Nancy Johnson', 3, 85000),
      ('John Doe', 2, 45000),
      ('Lily Bush', 3, 55000);

2.1 使用INNER JOIN子句的MySQL UPDATE JOIN範例

假設想根據員工的工作表現來調整員工的工資。

因此，優點百分比儲存在merits表中，您必須使用UPDATE INNER JOIN語句根據儲存在merits表中的百分比來調整employees表中員工的工資。

employees和merits表之間以是performance欄位相關聯的。 請參閱以下查詢：

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

上面查詢語句的工作原理是什麼？

我們僅在UPDATE子句之後指定employees表，因為我們只想更新employees表中的資料。

對於employees表中的每一行，查詢根據merits表中performance列中的值來檢查employees表中的performance列中的值。 如果找到一個匹配，它將獲得merits表中的百分比，並更新employees表中的salary列。

mysql> select * from employees; -- 更新之前的資料
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Minsu   |           3 |  65000 |
|      3 | Sue Greenspan |           4 |  75000 |
|      4 | Grace Dell    |           5 | 125000 |
|      5 | Nancy Johnson |           3 |  85000 |
|      6 | John Doe      |           2 |  45000 |
|      7 | Lily Bush     |           3 |  55000 |
+--------+---------------+-------------+--------+
7 rows in set

mysql> UPDATE employees
        INNER JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * percentage; -- 執行連線更新
Query OK, 6 rows affected
Rows matched: 7  Changed: 6  Warnings: 0

mysql> select * from employees; -- 更新之後的資料
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Minsu   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
+--------+---------------+-------------+--------+
7 rows in set

因為省略了UPDATE語句中的WHERE子句，所以employees表中的所有記錄都被更新。

2.2 具有LEFT JOIN的MySQL UPDATE JOIN範例

假設公司又雇用了兩名新員工：

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

因為這些員工是新員工，所以他們的績效(performance)資料不可用或為NULL。現在 employees表中的資料，如下所示 - 

mysql> SELECT * FROM employees;
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Minsu   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
|      8 | Jack William  | NULL        |  43000 |
|      9 | Ricky Bond    | NULL        |  52000 |
+--------+---------------+-------------+--------+
9 rows in set

要計算新員工的工資，不能使用UPDATE INNER JOIN語句，因為它們的績效資料在merits表中不可用。這就是為什麼要使用UPDATE LEFT JOIN來實現了。

當UPDATE LEFT JOIN語句在另一個表中沒有相應行時，就會更新表中的一行。

例如，您可以使用以下語句將新雇員的工資增加1.5％：

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

上面語句執行後，得到以下結果 - 

mysql> SELECT * FROM employees;
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Minsu   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
|      8 | Jack William  | NULL        |  43000 |
|      9 | Ricky Bond    | NULL        |  52000 |
+--------+---------------+-------------+--------+
9 rows in set

mysql> UPDATE employees
        LEFT JOIN
    merits ON employees.performance = merits.performance 
SET 
    salary = salary + salary * 0.015
WHERE
    merits.percentage IS NULL;
Query OK, 2 rows affected
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from employees;
+--------+---------------+-------------+--------+
| emp_id | emp_name      | performance | salary |
+--------+---------------+-------------+--------+
|      1 | Mary Doe      |           1 |  50000 |
|      2 | Cindy Minsu   |           3 |  66950 |
|      3 | Sue Greenspan |           4 |  78750 |
|      4 | Grace Dell    |           5 | 135000 |
|      5 | Nancy Johnson |           3 |  87550 |
|      6 | John Doe      |           2 |  45450 |
|      7 | Lily Bush     |           3 |  56650 |
|      8 | Jack William  | NULL        |  43645 |
|      9 | Ricky Bond    | NULL        |  52780 |
+--------+---------------+-------------+--------+
9 rows in set

在本教學中，我們向您展示了如何使用MySQL UPDATE JOIN與INNER JOIN和LEFT JOIN子句來執行跨表更新。