《MySQL 入门教程》第 24 篇 DML 语句之更新数据

上一篇我们介绍了如何使用 INSERT 语句插入数据，本篇继续学习 MySQL 中的数据更新操作，也就是UPDATE语句。

24.1 单表更新

MySQL 使用 UPDATE 语句更新表中的数据，基本的语法如下：

UPDATE table_name
   SET col1 = expr1,
       col2 = expr2,
       ...
[WHERE conditions]
[ORDER BY ...]
[LIMIT row_count];

其中，table_name 是表名；SET子句指定了需要更新的列和更新后的值（expr1、expr2 或者 DEFAULT），多个字段使用逗号进行分隔；只有满足 WHERE 条件的数据行才会被更新，如果没有指定条件将会更新表中所有行；如果指定了 ORDER BY 子句，按照顺序更新数据行；如果指定了 LIMIT 子句，最多会更新 row_count 行数据。

例如，以下语句为 emp_devp 表中“关平”增加 10% 的月薪和 2000 奖金：

UPDATE emp_devp
SET salary = salary * 1.1,
    bonus = 2000
WHERE emp_name = '关平';

SELECT emp_name, salary, bonus
FROM emp_devp
WHERE emp_name = '关平';

emp_name|salary |bonus  |
--------|-------|-------|
关平     |7480.00|2000.00|

从查询结果可以看出，“关平”的月薪增加到了 7480，奖金被修改为 2000。

和插入数据一样，更新数据时也会执行约束校验，确保不会产生违反约束的数据。例如，以下更新语句违反了外键约束：

mysql> UPDATE IGNORE employee
    -> SET dept_id = 10
    -> WHERE emp_id = 1;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`hrdb`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`))

由于不存在在编号为 10 的部门，所以无法将员工的 dept_id 字段设置为 10。此时，如果在 UPDATE 语句中使用了IGNORE选项，将会忽略执行过程的中错误，当然也不会更新成功。例如：

mysql> UPDATE IGNORE employee
    -> SET dept_id = 10
    -> WHERE emp_id = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                    |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (`hrdb`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`dept_id`) REFERENCES `department` (`dept_id`)) |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

如果指定了 ORDER BY 子句，MySQL 将会按照指定顺序更新数据。这种方式有一些特殊用途，例如：

DROP TABLE IF EXISTS t;
CREATE TABLE t(id int NOT NULL PRIMARY KEY);
INSERT INTO t VALUES (1),(2),(3);

UPDATE t
SET id = id + 1;
ERROR 1062 (23000): Duplicate entry '2' for key 't.PRIMARY'

由于 id 字段是主键，更新时会产生唯一键冲突。为了避免这个问题，可以将 id 的值按照从大到小的顺序进行更新：

UPDATE t
SET id = id + 1
ORDER BY id DESC;

SELECT * FROM t;
id|
--|
 2|
 3|
 4|

对于多个字段的更新，计算的时候按照从左到右的顺序赋值。例如：

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

其中，col2 的值等于 col1 更新后的数据；也就是说，以上语句的执行结果是 col2 和 col1 的值相同。

24.2 跨表更新

连接查询（JOIN ）可以用于从多个表中返回关联的数据。同样，UPDATE JOIN语句可以通过关联其他表中的数据进行更新。

UPDATE table_name t
[INNER JOIN | LEFT JOIN] other_table ON conditions
SET t.col1 = expr1,
    t.col2 = expr2
WHERE conditions
[ORDER BY ...]
[LIMIT row_count];

其中，table_name 是需要更新数据的表名；INNER JOIN或者LEFT JOIN用于连接其他的表；SET子句指定了需要更新的列和更新后的值，表达式 expr1、expr2 等可以引用 other_table 中的字段；只有满足 WHERE 条件的数据行才会被更新，如果没有指定条件将会更新表中所有行；如果指定了 ORDER BY 子句，按照顺序更新数据行；如果指定了 LIMIT 子句，最多会更新 row_count 行数据。

例如，以下语句通过关联 employee 中的数据更新 emp_devp 中的月薪：

UPDATE emp_devp ed
JOIN employee d ON (d.emp_id = ed.emp_id)
SET ed.salary = d.salary;

以上关联更新语句也可以通过一个子查询实现：

UPDATE emp_devp ed
   SET salary = (SELECT e.salary
                 FROM employee e
                 WHERE e.emp_id = ed.emp_id);

以上语句在 SET 子句中使用了一个关联子查询，将 employee 表中开发部门所有员工的月薪更新到 emp_devp 表对应的记录中。

24.3 多表更新

MySQL 中的 UPDATE 语句支持同时更新多个表中的数据。例如：

UPDATE emp_devp ed
JOIN employee e ON (ed.emp_id = e.emp_id)
SET ed.salary = e.salary,
    e.bonus = 0;

该语句通过连接操作同时更新了 emp_devp 和 employee 中的数据。

对于多表更新操作，每个匹配的数据行只会更新一次，即使多次匹配连接条件。另外，多表更新语句不支持 ORDER BY 和 LIMIT 子句。

与单表更新不同的是，多表更新语句中多个字段的赋值不是从左至右顺序执行，而是顺序不确定。