Mysql联表update数据

晟数学苑 2021-10-21

1091

点击蓝字阅读更多干货

1.MySQL UPDATE 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)。

第二，指定一种要使用的连接，即INNER JOIN或LEFT JOIN和连接条件。JOIN子句必须出现在UPDATE子句之后。

第三，要为要更新的T1和/或T2表中的列分配新值。

第四，WHERE子句中的条件用于指定要更新的行。

2. 示例

首先，我们将在这些例子中使用一个新的示例数据库(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 表，因为我们只想更新 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表中的所有记录都被更新。如果需要 performance 等级大于1的员工才更新薪资，那么 sql 可以这样写：

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

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 语句（为什么不能，可参考sql之left join、right join、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> 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

示例

# 单表join
update bbs_uhome_card_activate ca INNER JOIN bbs_uhome_card_rules cr on ca.card_brach=cr.card_bach set ca.create_user=cr.create_user;
 
# 多表join
UPDATE bbs_uhome_card_order co INNER JOIN bbs_uhome_card_order_record cor on co.order_no=cor.order_no JOIN bbs_uhome_card_activate ca on cor.card_no=ca.card_no set co.create_user=ca.create_user

站在巨人肩膀上摘苹果

https://www.jianshu.com/p/60b3f987c477

往期推荐 ●●

// 1

｜Postgresql中order by limit 索引选择错误导致查询很慢

// 2

｜MySQL的主从复制

// 3

｜掌握TiUP工具之启停TiDB集群节点

// 4

｜Postgresql使用pg_rman恢复以及备份集的删除

我知道你“在看”哟～

mysql

文章转载自晟数学苑，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Mysql联表update数据

评论