S2B-SQL UPDATE 更新数据完全指南-99%的人忘记WHERE子句，SQL高手却这样写：从基础语法到多表关联的数据修改利器

S2B-SQL UPDATE 更新数据完全指南-99%的人忘记WHERE子句，SQL高手却这样写：从基础语法到多表关联的数据修改利器

摘要

99% 的人执行 UPDATE 时忘记 WHERE 子句，导致全表数据被误改？SQL 高手却掌握 WHERE 条件、子查询更新、多表关联等技巧，数据修改精准高效。本文档用生活化比喻解析 SQL UPDATE 更新数据，从基础语法到多表关联，帮你掌握数据修改核心技能，避免数据灾难。

---

目录

• 前置知识点
• 什么是 UPDATE？
• 问题描述
• 问题考察点
• 快速上手
• ️ UPDATE 基本语法
• 单表更新
• UPDATE 与表达式
• UPDATE 与子查询
• 多表关联更新
• 不同数据库语法差异
• ️ 注意事项和警告
• 性能优化
• 对比示例
• 常见错误与修正
• 实战应用案例
• 总结

---

前置知识点

基础知识点（必须掌握）

• 数据库表结构：理解表、字段、记录的概念
• 数据类型：了解常见数据类型（整数、字符串、日期等）
• SQL 基础语法：理解 SQL 语句的基本结构
• WHERE 子句：掌握 WHERE 条件筛选的基本用法（参见 S1D-SQL WHERE 条件筛选完全指南）

进阶知识点（建议了解）

• 约束（constraint）：了解主键（primary key）、外键（foreign key）、唯一性约束（unique constraint）的作用
• 子查询（subquery）：了解子查询的基本概念和使用方法
• 多表关联（JOIN）：了解表与表之间的关联关系
• 事务（transaction）：了解事务的基本概念，理解 COMMIT 和 ROLLBACK 的作用

学习建议

• 小白（零基础）：先理解表的基本概念，掌握 UPDATE 的基本语法，重点理解 WHERE 子句的重要性
• 初级（刚入门不久）：学会使用 UPDATE 更新单条记录和多条记录，掌握 WHERE 条件的使用
• 中级（入门一段时间）：掌握 UPDATE 与表达式、子查询更新、多表关联更新等高级技巧
• 高级（资深开发者）：理解 UPDATE 的性能优化、事务处理、行锁优化等高级特性

---

什么是 UPDATE？

核心概念

UPDATE（更新） 是 SQL 中用于修改表中已存在记录的语句。

生活化比喻：UPDATE 就像修改图书馆的书籍登记册（表）上已有的记录。比如某本书的借阅状态从"在馆"改为"已借出"，或者更新书籍的归还日期。UPDATE 不会新增记录，只会修改现有记录的内容。

为什么需要 UPDATE？

不使用 UPDATE 会怎样？

问题场景：学生表中某个学生的成绩录入错误，需要修改

不使用 UPDATE：

• 无法修改已存在的数据
• 只能删除旧记录，再插入新记录（繁琐且容易出错）
• 无法批量更新多条记录
• 无法根据条件选择性更新

使用 UPDATE 的解决方案

-- 更新学生表中 id 为 1 的学生的成绩
UPDATE students 
SET score = 95 
WHERE id = 1;

优势：

• 可以直接修改已存在的数据
• 操作简单，一条语句即可完成
• 可以批量更新多条记录
• 可以根据条件选择性更新
• 可以同时更新多个字段

---

问题描述

真实场景

想象一下这样的场景：你在一家电商公司工作，负责维护用户信息表。某天，产品经理告诉你："我们需要把所有 VIP 用户的积分增加 100 分，并且更新他们的会员等级。"

新手做法：

• 先查询所有 VIP 用户
• 一条一条手动修改每条记录
• 或者写一个循环，逐条更新

问题：

• 效率低下，如果 VIP 用户有 10000 个，需要执行 10000 次更新操作
• 容易出错，可能漏掉某些用户
• 代码复杂，需要写循环逻辑

SQL 高手做法：

-- 一条 UPDATE 语句搞定
UPDATE users 
SET points = points + 100, 
    level = 'VIP' 
WHERE user_type = 'VIP';

优势：

• 一条语句完成所有更新
• 效率高，数据库自动优化执行
• 不会漏掉任何符合条件的记录
• 代码简洁，易于维护

核心痛点

1. 忘记 WHERE 子句：99% 的人在执行 UPDATE 时忘记添加 WHERE 条件，导致全表数据被误改
2. 不知道如何批量更新：面对大量数据需要更新时，不知道如何高效处理
3. 多表关联更新困难：需要根据其他表的数据来更新当前表时，不知道如何写 SQL
4. 性能问题：更新大量数据时，不知道如何优化性能

---

问题考察点

通过本文档的学习，你将掌握以下核心知识点：

基础能力 Must

1. UPDATE 基本语法：掌握 UPDATE 语句的基本结构和语法规则
2. WHERE 子句使用：理解 WHERE 子句的重要性，掌握条件筛选的写法
3. 单表更新：学会更新单条记录、多条记录，以及更新多个字段

进阶能力 ⭐ Should

4. 表达式更新：掌握在 UPDATE 中使用表达式进行计算更新
5. 子查询更新：学会使用子查询来更新数据，理解 EXISTS 条件的重要性
6. 多表关联更新：掌握 MySQL、SQL Server 等不同数据库的多表关联更新语法

高级能力 Could

7. 性能优化：了解索引使用、分批更新、事务处理、行锁优化等性能优化技巧
8. 不同数据库差异：了解 MySQL、Oracle、SQL Server、PostgreSQL 等数据库的语法差异
9. 实战应用：掌握各种实际业务场景下的 UPDATE 应用技巧

---

快速上手

让我们通过一个最简单的例子来快速了解 UPDATE 语句的使用方法。

最简单的 UPDATE 示例

假设我们有一个学生表 students，现在需要将 id 为 1 的学生的成绩更新为 95 分。

-- 更新学生表中 id 为 1 的学生的成绩
UPDATE students 
SET score = 95 
WHERE id = 1;

逐行代码解释：

-- UPDATE 作用：指定要更新数据的表
UPDATE students 

-- SET 作用：指定要更新的字段和新值
SET score = 95 

-- WHERE 作用：指定更新条件，只更新 id 为 1 的记录
WHERE id = 1;

执行结果：

• 如果表中存在 id 为 1 的记录，该记录的 score 字段会被更新为 95
• 如果表中不存在 id 为 1 的记录，不会报错，但也不会有任何记录被更新

️ 重要提醒：WHERE 子句非常重要！如果省略 WHERE 子句，所有记录的 score 字段都会被更新为 95，这可能导致数据灾难！

---

️ UPDATE 基本语法

基本语法结构

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

语法说明：

• UPDATE：更新命令关键字
• table_name：要更新数据的表名
• SET：设置关键字，用于指定要更新的字段和新值
• column1, column2, ...：要更新的字段名称，可以同时更新多个字段
• value1, value2, ...：对应字段的新值
• WHERE：条件关键字（可选，但强烈建议使用）
• condition：更新条件，用于指定哪些记录需要更新

生活化比喻：UPDATE 就像修改登记册，UPDATE table_name 是"在哪个登记册上"，SET column = value 是"把哪个字段改成什么值"，WHERE condition 是"只修改符合条件的记录"。

语法变体

另一种语法格式（中文注释版）：

UPDATE 表名称 
SET 列名称 = 新值 
WHERE 列名称 = 某值

参数详解

参数	说明	是否必需	示例
table_name	要修改的表名称	必需	students
column	要修改的字段名称	必需	score
value	要修改的新值	必需	95
WHERE condition	修改条件	️ 强烈建议	WHERE id = 1

️ 重要警告：WHERE 子句虽然不是语法上必需的，但在实际使用中强烈建议必须使用。如果省略 WHERE 子句，所有记录都将被更新！

---

单表更新

让我们来详细学习单表更新的各种用法。单表更新是最常用的 UPDATE 操作，包括更新单条记录、多条记录等场景。

1️⃣ 更新单条记录（单字段） Must

场景：更新某个学生的成绩

语法：

UPDATE table_name
SET column = value
WHERE condition;

实际例子：

-- 更新学生表中 id 为 1 的学生的成绩为 95 分
UPDATE students 
SET score = 95 
WHERE id = 1;

逐行代码解释：

-- UPDATE students：指定要更新 students 表
UPDATE students 

-- SET score = 95：将 score 字段的值设置为 95
SET score = 95 

-- WHERE id = 1：只更新 id 等于 1 的记录
WHERE id = 1;

更多示例：

-- 示例 1：更新客户表中 id 为 1 的客户的联系人和城市
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City = 'Frankfurt'
WHERE CustomerID = 1;

-- 示例 2：更新人员表中姓为 'Wilson' 的人的名字
UPDATE Person 
SET FirstName = 'Fred' 
WHERE LastName = 'Wilson';

-- 示例 3：更新用户表中 id 为 1 的用户名
UPDATE users
SET username = '新用户名'
WHERE user_id = 1;

2️⃣ 更新单条记录（多字段） Must

场景：同时更新某个学生的多个字段信息

语法：

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

实际例子：

-- 更新学生表中 id 为 2 的学生的姓名和性别
UPDATE students 
SET name = 'mike', sex = '1' 
WHERE id = '2';

逐行代码解释：

-- UPDATE students：指定要更新 students 表
UPDATE students 

-- SET name = 'mike', sex = '1'：同时更新 name 和 sex 两个字段
-- name 字段设置为 'mike'，sex 字段设置为 '1'
SET name = 'mike', sex = '1' 

-- WHERE id = '2'：只更新 id 等于 '2' 的记录
WHERE id = '2';

更多示例：

-- 示例 1：更新人员表中姓为 'Wilson' 的人的地址和城市
UPDATE Person 
SET Address = 'Zhongshan 23', City = 'Nanjing' 
WHERE LastName = 'Wilson';

-- 示例 2：更新客户表中 id 为 2 的客户的多个地址字段
UPDATE client
SET rue = '49 Rue Ameline',
    ville = 'Saint-Eustache-la-Forêt',
    code_postal = '76210'
WHERE id = 2;

3️⃣ 更新多条记录 ⭐ Should

场景：批量更新符合条件的所有记录

语法：

UPDATE table_name
SET column = value
WHERE condition;

实际例子：

-- 更新所有来自 'Mexico' 的客户的联系人
UPDATE Customers
SET ContactName = 'Juan'
WHERE Country = 'Mexico';

逐行代码解释：

-- UPDATE Customers：指定要更新 Customers 表
UPDATE Customers

-- SET ContactName = 'Juan'：将所有符合条件的记录的 ContactName 设置为 'Juan'
SET ContactName = 'Juan'

-- WHERE Country = 'Mexico'：只更新 Country 等于 'Mexico' 的记录
-- 注意：这里可能有多条记录符合条件，都会被更新
WHERE Country = 'Mexico';

更多示例：

-- 示例 1：更新 id 在 5 到 7 之间的学生的姓名和成绩
UPDATE students 
SET name = '小牛', score = 77 
WHERE id >= 5 AND id <= 7;

-- 示例 2：将所有价格低于 100 的商品价格提高 10%
UPDATE products 
SET price = price * 1.1 
WHERE price < 100;

-- 示例 3：将所有成绩低于 80 的学生的成绩加 10 分
UPDATE students 
SET score = score + 10 
WHERE score < 80;

4️⃣ 更新所有记录（危险操作） ️

️ 严重警告：如果不加 WHERE 条件，则所有记录都会被更新！这是一个非常危险的操作！

错误示例：

--  危险！这会更新表中所有记录的 ContactName
UPDATE Customers
SET ContactName = 'Juan';

执行结果：表中所有记录的 ContactName 字段都会被更新为 'Juan'，这通常不是我们想要的结果！

另一个错误示例：

--  危险！这会更新表中所有记录的 pays 字段
UPDATE client
SET pays = 'FRANCE';

执行结果：表中所有记录的 pays 字段都会被更新为 'FRANCE'。

正确做法：

• 在执行 UPDATE 语句前，先用 SELECT 语句来测试 WHERE 条件是否筛选出了期望的记录集
• 对于重要的数据更新操作，建议先在测试环境中执行，确认无误后再在生产环境中执行
• 在 MySQL 中可以通过设置 sql_safe_updates = 1 来强制要求 WHERE 条件

MySQL 安全模式设置：

-- 开启安全更新模式，强制要求 WHERE 条件
SET sql_safe_updates = 1;

开启后，如果执行没有 WHERE 条件的 UPDATE 语句，MySQL 会报错，从而避免误操作。

---

UPDATE 与表达式

在 UPDATE 语句中，更新字段时可以使用表达式进行计算。

表达式更新示例

场景：给所有成绩低于 80 的学生的成绩加 10 分

语法：

UPDATE table_name
SET column = expression
WHERE condition;

实际例子：

-- 将所有成绩低于 80 的学生的成绩加 10 分
UPDATE students 
SET score = score + 10 
WHERE score < 80;

逐行代码解释：

-- UPDATE students：指定要更新 students 表
UPDATE students 

-- SET score = score + 10：将当前行的 score 字段的值加上 10
-- 注意：这里使用了表达式 score + 10，而不是直接赋值
SET score = score + 10 

-- WHERE score < 80：只更新成绩低于 80 的记录
WHERE score < 80;

表达式说明：

• score + 10 是一个表达式，表示将当前记录的 score 值加上 10
• 表达式可以使用 +、-、*、/ 等运算符
• 表达式可以引用当前记录的字段值

更多示例：

-- 示例 1：将所有价格低于 100 的商品价格提高 10%
UPDATE products 
SET price = price * 1.1 
WHERE price < 100;

-- 示例 2：给所有用户的积分增加 10 分（无条件，更新所有记录）
UPDATE users
SET points = points + 10;

️ 注意事项：

• 表达式中的字段名会引用当前记录的值
• 表达式计算是在更新时进行的，不是查询时
• 使用表达式更新时，要确保表达式的结果类型与字段类型兼容

---

UPDATE 与子查询

UPDATE 语句可以与子查询结合使用，根据其他表的数据来更新当前表。这是 UPDATE 的高级用法之一。

1️⃣ 使用子查询更新单字段 ⭐ Should

场景：根据部门表更新员工表的部门名称

语法：

UPDATE table1 
SET column1 = (SELECT column FROM table2 [WHERE condition])
WHERE table1.column2 = value;

实际例子：

-- 根据部门表更新员工表的部门名称
UPDATE emp e
SET e.dname = (SELECT d.dname FROM dept d WHERE e.deptno = d.deptno);

逐行代码解释：

-- UPDATE emp e：指定要更新 emp 表，使用别名 e
UPDATE emp e

-- SET e.dname = (子查询)：将 dname 字段设置为子查询的结果
-- 子查询：从 dept 表中查询与当前员工部门编号匹配的部门名称
SET e.dname = (SELECT d.dname FROM dept d WHERE e.deptno = d.deptno)

-- 注意：这里没有 WHERE 条件，会更新所有记录
-- 但子查询中的条件 e.deptno = d.deptno 确保了只更新有匹配部门的记录

更多示例：

-- 示例 1：根据订单表更新订单状态
UPDATE orders 
SET status = 'Shipped' 
WHERE customer_id = (SELECT id FROM customers WHERE name = 'John Doe');

-- 示例 2：根据详情表更新订单表的订单名称
UPDATE t_order t1
SET ordername = (SELECT detailname FROM t_detail 
                 WHERE t_detail.detailclasses = t1.classes)
WHERE t1.orderid = 1;

2️⃣ 使用子查询更新多字段 ⭐ Should

场景：同时根据其他表更新多个字段

语法（Oracle）：

UPDATE table1 alias
SET (column_name, column_name) = (
    SELECT column_name, column_name 
    FROM table2 
    WHERE table2.column_name = alias.column_name
)
[WHERE column_name = VALUE]

实际例子：

-- 同时更新学生表的姓名和性别（Oracle 语法）
UPDATE stu t 
SET (t.NAME, t.SEX) = (SELECT t1.NAME, t1.SEX 
                       FROM stu1 t1 
                       WHERE t1.ID = t.ID)
WHERE EXISTS(SELECT 1 FROM stu1 t2 WHERE t2.ID = t.ID);

逐行代码解释：

-- UPDATE stu t：指定要更新 stu 表，使用别名 t
UPDATE stu t 

-- SET (t.NAME, t.SEX) = (子查询)：同时更新 NAME 和 SEX 两个字段
-- 子查询返回两个字段：NAME 和 SEX
SET (t.NAME, t.SEX) = (SELECT t1.NAME, t1.SEX 
                       FROM stu1 t1 
                       WHERE t1.ID = t.ID)

-- WHERE EXISTS(...)：只更新在 stu1 表中存在对应 ID 的记录
-- 这是非常重要的！如果不加 EXISTS 条件，不匹配的记录会被更新为 NULL
WHERE EXISTS(SELECT 1 FROM stu1 t2 WHERE t2.ID = t.ID);

️ 重要注意事项：

多表关联 UPDATE 的时候，记得要加 EXISTS() 条件，否则不满足条件的记录被 UPDATE 成 NULL。

错误示例：

--  错误！没有 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID);

问题：如果 stu 表中存在某个 ID，但 stu1 表中不存在对应的 ID，那么子查询会返回 NULL，导致 stu 表中该记录的 NAME 字段被更新为 NULL。

正确做法：

--  正确！添加了 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID)
WHERE EXISTS(SELECT 1 FROM stu1 t2 WHERE t2.ID = t.ID);

更多示例：

-- 示例 1：同时更新订单表的订单名称和订单价格
UPDATE t_order t1
SET (ordername, orderprice) = (
    SELECT detailname, totalprice 
    FROM t_detail 
    WHERE t_detail.detailclasses = t1.classes
)
WHERE t1.orderid = 1;

-- 示例 2：同时更新表 A 的三个字段
UPDATE A 
SET (A1, A2, A3) = (SELECT B1, B2, B3 FROM B WHERE A.ID = B.ID)
WHERE ID IN (SELECT B.ID FROM B WHERE A.ID = B.ID);

---

多表关联更新

多表关联更新是指根据多个表之间的关联关系来更新数据。不同数据库的语法略有不同。

1️⃣ MySQL 多表关联更新 ⭐ Should

语法：

UPDATE table1 
INNER|LEFT|RIGHT JOIN table2 
ON condition
SET column1 = value1, column2 = value2, ...
[WHERE conditions];

实际例子：

-- 根据用户收入表更新用户余额表
UPDATE $table1 a 
INNER JOIN $table2 b
ON a.user_id = b.user_id 
SET a.balance = a.balance + b.income, b.status = 1 
WHERE b.id = 5 AND b.status = 0;

逐行代码解释：

-- UPDATE $table1 a：指定要更新的主表，使用别名 a
UPDATE $table1 a 

-- INNER JOIN $table2 b ON a.user_id = b.user_id：关联表2，关联条件是 user_id 相等
INNER JOIN $table2 b
ON a.user_id = b.user_id 

-- SET a.balance = a.balance + b.income, b.status = 1：更新两个表的字段
-- 表1的 balance 字段增加表2的 income 值
-- 表2的 status 字段设置为 1
SET a.balance = a.balance + b.income, b.status = 1 

-- WHERE b.id = 5 AND b.status = 0：只更新符合条件的记录
WHERE b.id = 5 AND b.status = 0;

更多示例：

-- 示例 1：使用子查询作为关联表
UPDATE A 
INNER JOIN (SELECT B.B1 as B1, B.B2 as B2, C.C1 as C1 
            FROM B 
            LEFT JOIN C ON B.B3 = C.C3) as t
ON A.A3 = t.B1
SET A.A1 = t.B2, A.A2 = t.C1;

-- 示例 2：根据图书信息和部门信息更新书架表
UPDATE tb_bookcase 
INNER JOIN (SELECT tb_bookinfo.rid as rid, tb_bookinfo.bookname, department.name 
            FROM tb_bookinfo 
            LEFT JOIN department ON tb_bookinfo.depid = department.id) as t
ON tb_bookcase.id = t.rid
SET tb_bookcase.bookname = t.bookname, tb_bookcase.departname = t.name;

2️⃣ SQL Server 多表关联更新 ⭐ Should

语法：

UPDATE table1 
SET column1 = t2.column1, 
    column2 = t2.column2,
    ...  
FROM table1 
INNER/LEFT/RIGHT JOIN table2 
ON table1.column = table2.column
[WHERE conditions]

实际例子：

-- 根据表 B 和表 C 的数据更新表 A
UPDATE A
SET A1 = t2.B2, A2 = t2.C1
FROM A 
INNER JOIN (SELECT B.B1, B.B2, C.C1 
            FROM B 
            LEFT JOIN C ON B.B3 = C.C3) t2 
ON A.A3 = t2.B1
WHERE A.A4 = 1;

逐行代码解释：

-- UPDATE A：指定要更新的表
UPDATE A

-- SET A1 = t2.B2, A2 = t2.C1：设置要更新的字段
SET A1 = t2.B2, A2 = t2.C1

-- FROM A INNER JOIN ...：在 FROM 子句中指定关联关系
FROM A 
INNER JOIN (SELECT B.B1, B.B2, C.C1 
            FROM B 
            LEFT JOIN C ON B.B3 = C.C3) t2 
ON A.A3 = t2.B1

-- WHERE A.A4 = 1：指定更新条件
WHERE A.A4 = 1;

更多示例：

-- 示例 1：根据订单表和详情表更新异常费用表
UPDATE t_abnormal_fee
SET order_code = t2.order_code, return_fee = t2.express_fee
FROM t_abnormal_fee 
INNER JOIN (SELECT t_order.id, t_order.order_code, t_detail.express_fee 
            FROM t_order 
            LEFT JOIN t_detail ON t_order.name = t_detail.name) t2 
ON t_abnormal_fee.id = t2.id
WHERE t_abnormal_fee.id = 1;

3️⃣ 使用子查询进行关联更新 ⭐ Should

场景：使用子查询方式实现多表关联更新

语法：

UPDATE t1 
SET t1.`name` = (SELECT t2.`name` FROM t2 WHERE t1.id = t2.id)
WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.id = t1.id);

实际例子：

-- 根据表 t2 更新表 t1 的 name 字段
UPDATE t1 
SET t1.`name` = (SELECT t2.`name` FROM t2 WHERE t1.id = t2.id)
WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.id = t1.id);

逐行代码解释：

-- UPDATE t1：指定要更新的表
UPDATE t1 

-- SET t1.`name` = (子查询)：将 name 字段设置为子查询的结果
SET t1.`name` = (SELECT t2.`name` FROM t2 WHERE t1.id = t2.id)

-- WHERE EXISTS(...)：只更新在 t2 表中存在对应 id 的记录
-- 这是非常重要的！避免将不匹配的记录更新为 NULL
WHERE EXISTS(SELECT 1 FROM t2 WHERE t2.id = t1.id);

---

不同数据库语法差异

不同数据库对 UPDATE 语句的支持略有不同，特别是在多表关联更新方面。让我们来看看主要数据库的差异。

MySQL

特点：

• 支持使用 JOIN 进行多表更新
• 可以通过设置 sql_safe_updates 强制要求 WHERE 条件
• UPDATE 语句会返回更新的行数

多表更新语法：

UPDATE table1 
INNER JOIN table2 ON condition
SET column1 = value1
WHERE condition;

安全模式设置：

-- 开启安全更新模式，强制要求 WHERE 条件
SET sql_safe_updates = 1;

返回结果示例：

-- 更新 id=1 的记录
mysql> UPDATE students SET name='大宝' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 更新 id=999 的记录（不存在）
mysql> UPDATE students SET name='大宝' WHERE id=999;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Oracle

特点：

• 支持使用子查询更新多字段
• 语法较为特殊，使用括号包裹多个字段

多字段更新语法：

UPDATE table1 alias
SET (column_name, column_name) = (
    SELECT column_name, column_name 
    FROM table2 
    WHERE table2.column_name = alias.column_name
)
[WHERE column_name = VALUE]

SQL Server

特点：

• 支持使用 FROM 子句进行多表更新
• 语法与 MySQL 不同，UPDATE 和 FROM 分开写

多表更新语法：

UPDATE table1 
SET column1 = t2.column1
FROM table1 
INNER JOIN table2 ON condition
WHERE condition;

PostgreSQL

特点：

• 支持使用 ON CONFLICT 进行插入或更新操作（UPSERT）
• 语法较为灵活

UPSERT 语法：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_column) 
DO UPDATE SET column1 = EXCLUDED.column1;

语法对比表

数据库	多表更新语法	特点
MySQL	UPDATE t1 JOIN t2 ON ... SET ...	支持 JOIN，语法简洁
SQL Server	UPDATE t1 SET ... FROM t1 JOIN t2 ON ...	使用 FROM 子句
Oracle	UPDATE t1 SET (col1, col2) = (SELECT ...)	支持多字段子查询更新
PostgreSQL	UPDATE t1 SET ... FROM t2 WHERE ...	类似 SQL Server，支持 UPSERT

---

️ 注意事项和警告

在使用 UPDATE 语句时，有一些重要的注意事项和警告需要牢记。

1️⃣ WHERE 子句的重要性 ️

重要警告：在更新记录时要格外小心！如果省略了 WHERE 子句，所有的记录都将被更新！

错误示例：

--  危险！这会更新所有记录
UPDATE Customers
SET ContactName = 'Juan';

执行结果：表中所有记录的 ContactName 字段都会被更新为 'Juan'，这通常不是我们想要的结果！

正确做法：

• 始终使用 WHERE 子句指定更新条件
• 在执行 UPDATE 前，先用 SELECT 语句测试 WHERE 条件
• 在 MySQL 中开启 sql_safe_updates 模式

执行没有 WHERE 子句的 UPDATE 要慎重，再慎重！

2️⃣ 更新前检查

在执行 UPDATE 语句时，务必谨慎，因为一旦执行，就会直接修改数据库中的数据。

建议流程：

1. 先用 SELECT 测试：

-- 第一步：先用 SELECT 查看会更新哪些记录
SELECT * FROM students WHERE score < 80;

-- 第二步：确认无误后，再执行 UPDATE
UPDATE students 
SET score = score + 10 
WHERE score < 80;

2. 测试环境验证：

   • 对于重要的数据更新操作，建议先在测试环境中执行
   • 确认无误后再在生产环境中执行

3. 备份数据：

   • 更新重要数据前，建议先备份相关表
   • 这样可以在出错时快速恢复

3️⃣ 多表关联更新的注意事项

️ 重要提醒：多表关联 UPDATE 的时候，记得要加 EXISTS() 条件，否则不满足条件的记录被 UPDATE 成 NULL。

错误示例：

--  错误！没有 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID);

问题：如果 stu 表中存在某个 ID，但 stu1 表中不存在对应的 ID，那么子查询会返回 NULL，导致 stu 表中该记录的 NAME 字段被更新为 NULL。

正确做法：

--  正确！添加了 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID)
WHERE EXISTS(SELECT 1 FROM stu1 t2 WHERE t2.ID = t.ID);

4️⃣ 无匹配记录的处理

如果 WHERE 条件没有匹配到任何记录，UPDATE 语句不会报错，也不会有任何记录被更新。

示例：

-- 更新 id 为 999 的记录（假设不存在）
UPDATE students 
SET score = 100 
WHERE id = 999;

执行结果：

• 不会报错
• 不会更新任何记录
• MySQL 会返回 Rows matched: 0 Changed: 0

建议：如果需要确认是否有记录被更新，可以检查 UPDATE 语句的返回结果。

5️⃣ 设置字段为 NULL

为了删除某个列的值，可设置它为 NULL（假定表定义允许 NULL 值）。

语法：

UPDATE table_name
SET column_name = NULL
WHERE condition;

示例：

-- 将某个学生的备注字段清空
UPDATE students 
SET remark = NULL 
WHERE id = 1;

注意事项：

• 只有允许 NULL 值的字段才能设置为 NULL
• 如果字段有 NOT NULL 约束，设置为 NULL 会报错

---

性能优化

在实际应用中，UPDATE 语句的性能优化非常重要，特别是当需要更新大量数据时。

1️⃣ 索引的使用 Could

对于 UPDATE 语句中涉及的列，确保相应的列上建有索引，以提高检索速度。

示例：

-- 如果经常根据 id 更新记录，确保 id 字段有索引
-- 创建索引
CREATE INDEX idx_students_id ON students(id);

-- 使用索引的 UPDATE 语句会更快
UPDATE students 
SET score = 95 
WHERE id = 1;  -- id 字段有索引，查询速度更快

注意事项：

• WHERE 条件中的字段应该有索引
• 但索引也会影响 UPDATE 的性能（需要更新索引）
• 需要权衡查询速度和更新速度

2️⃣ 分批更新 Could

当需要更新大量数据时，可以分批进行更新，避免一次性更新过多数据影响性能。

示例：

-- 方式 1：使用 LIMIT 分批更新（MySQL）
UPDATE students 
SET score = score + 10 
WHERE score < 80
LIMIT 1000;  -- 每次只更新 1000 条

-- 方式 2：使用循环分批更新
-- 第一次更新 id 1-1000
UPDATE students 
SET score = score + 10 
WHERE score < 80 AND id BETWEEN 1 AND 1000;

-- 第二次更新 id 1001-2000
UPDATE students 
SET score = score + 10 
WHERE score < 80 AND id BETWEEN 1001 AND 2000;

优势：

• 减少锁表时间
• 降低对数据库性能的影响
• 可以随时中断和恢复

3️⃣ 事务处理 Could

在更新大量数据时，考虑使用事务来确保数据的一致性。

示例：

-- 开始事务
START TRANSACTION;

-- 执行更新操作
UPDATE students 
SET score = score + 10 
WHERE score < 80;

-- 检查更新结果
SELECT COUNT(*) FROM students WHERE score >= 80;

-- 如果结果正确，提交事务
COMMIT;

-- 如果结果不正确，回滚事务
-- ROLLBACK;

优势：

• 确保数据一致性
• 可以回滚错误的更新
• 保证原子性操作

4️⃣ 行锁优化 Could

在 MySQL 中，当执行 UPDATE xxxx WHERE topic_id = xxx 时，MySQL 会对 topic_id 索引加行锁。

问题场景： 在高并发场景下，如果多个操作在同一个事务中，行锁的持有时间会延长。可以通过调整操作顺序来降低行锁的持有粒度。

优化示例：

原逻辑（性能较差）：

START TRANSACTION;
-- 第一步：UPDATE（加行锁）
UPDATE topics SET submit_count = submit_count + 1 WHERE topic_id = 1;
-- 第二步：INSERT（行锁一直持有，直到事务结束）
INSERT INTO results (topic_id, result) VALUES (1, 'AC');
COMMIT;

优化后（性能更好）：

START TRANSACTION;
-- 第一步：INSERT（不加行锁或加锁时间短）
INSERT INTO results (topic_id, result) VALUES (1, 'AC');
-- 第二步：UPDATE（行锁持有时间短）
UPDATE topics SET submit_count = submit_count + 1 WHERE topic_id = 1;
COMMIT;

实际案例： 通过调整 UPDATE 和 INSERT 的执行顺序，判题结果写库的逻辑从原来的 400 TPS 直接拉高到 2000 多 TPS。

原理：

• 先 INSERT 再 UPDATE，可以降低行锁的持有粒度
• 减少锁竞争，提高并发性能

5️⃣ 大数据更新的注意事项

大数据更新要注意：

• 索引：确保 WHERE 条件字段有索引
• 分批更新：避免一次性更新过多数据
• 事务：使用事务确保数据一致性

️ 重要提醒：如果更新条件不精准，可能导致大面积锁表，影响其他业务。

---

对比示例

通过对比示例，我们可以更清楚地看到 UPDATE 语句的价值和正确用法。

1️⃣ 不使用 UPDATE vs 使用 UPDATE

场景：需要修改学生表中某个学生的成绩

不使用 UPDATE（繁琐且容易出错）：

-- 第一步：删除旧记录
DELETE FROM students WHERE id = 1;

-- 第二步：插入新记录
INSERT INTO students (id, name, age, score) 
VALUES (1, '张三', 20, 95);

问题：

• 需要两步操作，繁琐
• 如果第二步失败，数据会丢失
• 无法保证数据一致性

使用 UPDATE（简单且安全）：

-- 一条语句完成更新
UPDATE students 
SET score = 95 
WHERE id = 1;

优势：

• 一条语句完成，简单
• 原子操作，保证数据一致性
• 不会丢失数据

2️⃣ 错误写法 vs 正确写法

场景：更新所有 VIP 用户的积分

错误写法（忘记 WHERE 条件）：

-- 危险！这会更新所有用户的积分
UPDATE users 
SET points = points + 100;

问题：

• 没有 WHERE 条件，所有记录都会被更新
• 可能导致数据灾难

正确写法（使用 WHERE 条件）：

-- 正确！只更新 VIP 用户的积分
UPDATE users 
SET points = points + 100 
WHERE user_type = 'VIP';

优势：

• 有 WHERE 条件，只更新符合条件的记录
• 精准更新，不会误改其他数据

3️⃣ 性能对比示例

场景：更新 10000 条记录的成绩

低效方式（逐条更新）：

-- 需要执行 10000 次 UPDATE 语句
UPDATE students SET score = 95 WHERE id = 1;
UPDATE students SET score = 95 WHERE id = 2;
UPDATE students SET score = 95 WHERE id = 3;
-- ... 重复 10000 次

问题：

• 执行 10000 次 SQL 语句，效率极低
• 网络开销大
• 数据库压力大

高效方式（批量更新）：

-- 一条语句更新所有记录
UPDATE students 
SET score = 95 
WHERE id BETWEEN 1 AND 10000;

优势：

• 一条语句完成，效率高
• 数据库自动优化执行
• 网络开销小

---

常见错误与修正

在使用 UPDATE 语句时，有一些常见的错误需要注意和避免。

1️⃣ 忘记 WHERE 子句

错误现象：

--  错误！忘记 WHERE 子句
UPDATE students 
SET score = 95;

问题：所有学生的成绩都会被更新为 95，这通常不是我们想要的结果！

修正方法：

--  正确！添加 WHERE 条件
UPDATE students 
SET score = 95 
WHERE id = 1;

预防措施：

• 在执行 UPDATE 前，先用 SELECT 测试 WHERE 条件
• 在 MySQL 中开启 sql_safe_updates 模式
• 养成习惯，每次写 UPDATE 时先写 WHERE 条件

2️⃣ 多表关联更新缺少 EXISTS

错误现象：

--  错误！没有 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID);

问题：如果 stu 表中存在某个 ID，但 stu1 表中不存在对应的 ID，那么该记录的 NAME 字段会被更新为 NULL。

修正方法：

--  正确！添加 EXISTS 条件
UPDATE stu t 
SET t.NAME = (SELECT t1.NAME FROM stu1 t1 WHERE t1.ID = t.ID)
WHERE EXISTS(SELECT 1 FROM stu1 t2 WHERE t2.ID = t.ID);

预防措施：

• 多表关联更新时，始终添加 EXISTS 条件
• 更新前先用 SELECT 测试子查询结果

3️⃣ 更新条件不精准导致锁表

错误现象：

--  错误！更新条件不精准，可能锁住大量记录
UPDATE orders 
SET status = 'Shipped' 
WHERE status = 'Pending';  -- 如果 Pending 订单有 100 万条

问题：如果符合条件的记录很多，可能导致大面积锁表，影响其他业务。

修正方法：

--  正确！分批更新，减少锁表时间
-- 第一次更新 id 1-10000
UPDATE orders 
SET status = 'Shipped' 
WHERE status = 'Pending' AND id BETWEEN 1 AND 10000;

-- 第二次更新 id 10001-20000
UPDATE orders 
SET status = 'Shipped' 
WHERE status = 'Pending' AND id BETWEEN 10001 AND 20000;

预防措施：

• 更新大量数据时，使用分批更新
• 添加更精确的 WHERE 条件
• 在业务低峰期执行大批量更新

4️⃣ 表达式类型不匹配

错误现象：

--  错误！字符串和数字直接相加
UPDATE students 
SET name = name + 10 
WHERE id = 1;

问题：字符串和数字类型不匹配，可能导致错误或意外结果。

修正方法：

--  正确！确保表达式类型匹配
UPDATE students 
SET score = score + 10 
WHERE id = 1;

预防措施：

• 确保 SET 子句中的表达式类型与字段类型兼容
• 使用类型转换函数（如 CAST、CONVERT）确保类型一致

---

实战应用案例

让我们通过一些实际的应用案例来加深对 UPDATE 语句的理解。

案例 1：更新用户信息

场景：用户修改个人资料，需要更新用户表中的信息

SQL 语句：

-- 更新用户表中 id 为 1 的用户名
UPDATE users
SET username = '新用户名'
WHERE user_id = 1;

说明：

• 这是最常见的 UPDATE 应用场景
• 通常用于用户修改个人信息、管理员修改用户信息等

案例 2：批量增加积分

场景：活动期间，给所有用户增加 10 积分

SQL 语句：

-- 给所有用户的积分增加 10 分
UPDATE users
SET points = points + 10;

说明：

• 使用表达式更新，将当前积分加上 10
• 没有 WHERE 条件，更新所有记录
• 适用于全用户活动奖励

案例 3：根据条件更新状态

场景：将某个客户的所有订单状态更新为"已发货"

SQL 语句：

-- 更新客户 id 为 1 的所有订单状态
UPDATE orders
SET status = 'Shipped'
WHERE customer_id = 1;

说明：

• 使用 WHERE 条件筛选特定客户的订单
• 可能更新多条记录
• 适用于批量状态更新

案例 4：使用表达式更新价格

场景：将所有价格低于 100 的商品价格提高 10%

SQL 语句：

-- 将价格低于 100 的商品价格提高 10%
UPDATE products
SET price = price * 1.1
WHERE price < 100;

说明：

• 使用表达式 price * 1.1 计算新价格
• WHERE 条件筛选价格低于 100 的商品
• 适用于价格调整、折扣计算等场景

案例 5：关联表更新

场景：根据部门表更新员工表的部门名称

SQL 语句：

-- 根据部门表更新员工表的部门名称
UPDATE emp e
SET e.dname = (SELECT d.dname FROM dept d WHERE e.deptno = d.deptno);

说明：

• 使用子查询从部门表获取部门名称
• 根据部门编号关联两个表
• 适用于数据同步、数据修复等场景

案例 6：线上调优案例（TPS 优化）

业务背景：一个类似于力扣在线做题的代码评测模块，用户提交判题任务后，后台会进行异步判题。

问题：大量的用户在前端提交后，一直都轮询不到判题结果。判题结果写库逻辑耗时 1 秒多。

原因分析：

• 更新这道题的提交数量、正确率等操作占据了 80% 的耗时
• 当执行 UPDATE xxxx WHERE topic_id = xxx 时，MySQL 会对 topic_id 索引加行锁
• 由于第一个步骤和第二个步骤又在同一个事务中，当高并发时，用户做题是多对一的关系，大量用户可能都在写一道题，造成题目 ID 的行锁竞争激烈
• 更新题目提交数、正确率的行锁在更新完后不会释放，还需等待第二步将结果写库完后（等事务执行完后），这样行锁的无效持有时间或者叫行锁的持有粒度就增加了

解决方案： 将 1、2 两个步骤交换下顺序：

• 原逻辑：先 UPDATE，再 INSERT
• 优化后：先 INSERT，再 UPDATE

这样可以降低行锁的持有粒度。

效果：判题结果写库的逻辑从原来的 400 TPS 直接拉高到 2000 多 TPS。

---

总结

通过本文档的学习，我们掌握了 SQL UPDATE 语句的核心知识点和实用技巧。让我们来总结一下关键要点：

核心要点回顾

1. UPDATE 基本语法 Must

   • UPDATE table_name SET column = value WHERE condition
   • WHERE 子句非常重要，省略会导致所有记录被更新

2. 单表更新 Must

   • 更新单条记录：使用精确的 WHERE 条件
   • 更新多条记录：使用范围或条件 WHERE 子句
   • 更新多个字段：在 SET 子句中用逗号分隔

3. 表达式更新 ⭐ Should

   • 可以在 SET 子句中使用表达式进行计算
   • 如：SET score = score + 10

4. 子查询更新 ⭐ Should

   • 使用子查询根据其他表的数据更新当前表
   • 多表关联更新时，记得添加 EXISTS 条件

5. 多表关联更新 ⭐ Should

   • MySQL：使用 JOIN 语法
   • SQL Server：使用 FROM 子句
   • Oracle：使用多字段子查询

6. 性能优化 Could

   • 使用索引提高查询速度
   • 分批更新大量数据
   • 使用事务确保数据一致性
   • 调整操作顺序降低行锁持有粒度

关键注意事项

• ️ WHERE 子句的重要性：执行没有 WHERE 子句的 UPDATE 要慎重，再慎重
• ️ 更新前检查：先用 SELECT 测试 WHERE 条件，确认无误后再执行 UPDATE
• ️ 多表关联更新：记得要加 EXISTS() 条件，避免字段被更新为 NULL
• ️ 大数据更新：注意索引、分批更新、事务处理

学习路径建议

1. 基础阶段：掌握 UPDATE 基本语法和 WHERE 子句使用
2. 进阶阶段：学习表达式更新、子查询更新、多表关联更新
3. 高级阶段：掌握性能优化技巧，理解不同数据库的语法差异

写在最后

UPDATE 语句是数据库中非常重要的操作之一，通过灵活运用 UPDATE 语句，我们可以高效地对数据库中的数据进行更新。在日常开发中，更新数据（UPDATE）是仅次于 SELECT 的常用 SQL 操作。

掌握 UPDATE 语句的关键在于：

• 理解 WHERE 子句的重要性
• 掌握各种更新技巧
• 注意性能优化
• 避免常见错误

希望本文档能够帮助你掌握 SQL UPDATE 语句的核心技能，在实际工作中灵活运用，提高开发效率，避免数据灾难。让我们一起在 SQL 学习的道路上不断进步！

---

作者：郑恩赐
机构：厦门工学院人工智能创作坊
日期：2025 年 11 月 11 日