mysql 语法 Update Join

在 mysql 中可以在使用 UPDATE 的时候配合 JOIN 。达到使用是使用另一个表中的数据去更新当前表的列的一个目的，或者是使用另一个表作为过滤条件。

基本语法如下：

UPDATE 表T1
[INNER | LEFT] JOIN 表T2 ON T1.列 = T2.列
SET 
    T1.目标列 = 表达式(可能包含T2的列),
    ...
WHERE 过滤条件;

UPDATE 表T1
[INNER | LEFT] JOIN 表T2 ON T1.列 = T2.列
SET 
    T1.目标列 = 表达式(可能包含T2的列),
    ...
WHERE 过滤条件;

SQL

我们将创建一个新的数据库表，然后以跟随实例来学习 Update Join

-- 创建部门表 (departments)
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    performance_factor DECIMAL(3, 2) -- 绩效系数，例如 1.10 表示 1.1倍
);

-- 创建员工表 (employees)
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
    last_raise_date DATE,
    status VARCHAR(20) DEFAULT 'ACTIVE'
);

-- 2. 插入模拟数据
-- ------------------------------------------
-- 插入部门：技术部系数高(1.2)，销售部中等(1.1)，人事部普通(1.0)
INSERT INTO departments VALUES 
(101, 'Technology', 1.20),
(102, 'Sales', 1.10),
(103, 'HR', 1.00);

-- 插入员工
INSERT INTO employees (emp_id, emp_name, dept_id, salary, last_raise_date) VALUES
(1, 'Alice', 101, 10000.00, '2023-01-01'), -- 技术部
(2, 'Bob', 102, 8000.00,  '2023-01-01'), -- 销售部
(3, 'Charlie', 101, 12000.00, '2023-06-01'), -- 技术部
(4, 'David', 103, 6000.00,  '2022-01-01'), -- 人事部
(5, 'Eve', 999, 7000.00, '2023-01-01');    -- 错误的部门ID (用于演示LEFT JOIN)

-- 创建部门表 (departments)
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    performance_factor DECIMAL(3, 2) -- 绩效系数，例如 1.10 表示 1.1倍
);

-- 创建员工表 (employees)
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10, 2),
    last_raise_date DATE,
    status VARCHAR(20) DEFAULT 'ACTIVE'
);

-- 2. 插入模拟数据
-- ------------------------------------------
-- 插入部门：技术部系数高(1.2)，销售部中等(1.1)，人事部普通(1.0)
INSERT INTO departments VALUES 
(101, 'Technology', 1.20),
(102, 'Sales', 1.10),
(103, 'HR', 1.00);

-- 插入员工
INSERT INTO employees (emp_id, emp_name, dept_id, salary, last_raise_date) VALUES
(1, 'Alice', 101, 10000.00, '2023-01-01'), -- 技术部
(2, 'Bob', 102, 8000.00,  '2023-01-01'), -- 销售部
(3, 'Charlie', 101, 12000.00, '2023-06-01'), -- 技术部
(4, 'David', 103, 6000.00,  '2022-01-01'), -- 人事部
(5, 'Eve', 999, 7000.00, '2023-01-01');    -- 错误的部门ID (用于演示LEFT JOIN)

SQL

使用场景1：关联更新

接下来我们要对员工进行一个涨薪的操作，涨薪的规则是员工原有的工资 * 所在部分的绩效系数。

UPDATE employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
SET 
    e.salary = e.salary * d.performance_factor,
    e.last_raise_date = CURDATE()
WHERE d.performance_factor > 1.0; -- 只更新系数大于1的部门

UPDATE employees e
INNER JOIN departments d ON e.dept_id = d.dept_id
SET 
    e.salary = e.salary * d.performance_factor,
    e.last_raise_date = CURDATE()
WHERE d.performance_factor > 1.0; -- 只更新系数大于1的部门

SQL

完成操作后，再次查看 employees 表，可以看到除了 David 和 Eve ，其他员工的薪资都上涨了。

使用场景2：处理缺失关联的数据

在 employees 中，Eve 的所在部门 id 为 999，显然这个部门是不存在的，我们可以使用 Update Join 将所有部分消息状态异常的员工的状态给修改一下。

UPDATE employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
SET e.status = 'UNKNOWN_DEPT'
WHERE d.dept_id IS NULL;

UPDATE employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
SET e.status = 'UNKNOWN_DEPT'
WHERE d.dept_id IS NULL;

SQL

再次查询 employees 表发现 Eve 的状态已经变为了 UNKOWN_DEPT。

使用场景3：跨表数据同步

现在我们想为 departments 表加一个字段 total_salary_cost 用来记录这个部分所有员工的工资和。

添加字段：ALTER TABLE departments ADD COLUMN total_salary_cost DECIMAL(15, 2) DEFAULT 0;

UPDATE departments d
INNER JOIN (
    SELECT dept_id, SUM(salary) as sum_salary
    FROM employees
    GROUP BY dept_id
) e_group ON d.dept_id = e_group.dept_id
SET d.total_salary_cost = e_group.sum_salary;

UPDATE departments d
INNER JOIN (
    SELECT dept_id, SUM(salary) as sum_salary
    FROM employees
    GROUP BY dept_id
) e_group ON d.dept_id = e_group.dept_id
SET d.total_salary_cost = e_group.sum_salary;