SQL内功笔记 · 第9篇：UPDATE FROM 进阶——告别逐行子查询，拥抱集合更新

前言为什么你需要UPDATE FROM在日常开发中我们经常会遇到这样的场景需要根据另一张表的数据来更新当前表的字段。新手通常的第一反应是写一个子查询UPDATE user_info SET dept_name (SELECT dept_name FROM dept WHERE id user_info.dept_id);这个写法虽然能工作但存在两个致命问题性能灾难子查询会为每一行都执行一次当数据量达到万级、十万级时更新速度会急剧下降功能局限当需要根据聚合结果如SUM、COUNT来更新时子查询写法会变得非常臃肿甚至难以实现而UPDATE FROM语法正是为了解决这两个痛点而生的。它采用集合操作的思路先通过JOIN将目标表和来源表关联成一张临时结果集然后一次性完成更新。无论是一对一的关联更新还是需要先GROUP BY聚合再更新UPDATE FROM都能以清晰、高效的 SQL 完成。PostgreSQL、SQL Server 等数据库原生支持这种语法而 MySQL 用户则需要用多表更新的方式来实现类似效果。这不是固定的语法而是一种通用的解决思路。语法差异语法差异说明PostgreSQL / SQL Server / SQLite 使用UPDATE ... FROM ...语法法MySQL 使用UPDATE ... JOIN ... SET ...语法本文以 PostgreSQL 语法为主MySQL 用户可将FROM子句改写为JOIN将WHERE关联条件移到ON中。。UPDATE FROM基础UPDATE语法:-- update sql结构UPDATEtable_nameSETcolumn1value1,column2value2,...WHEREcondition;UPDATE FROM用法:UPDATE目标表 tSET字段来源表.字段FROM来源表WHEREt.id来源表.id;PS:UPDATE的主表永远是目标表 t。demo的表结构createtableuser_info(idbigint,namevarchar(50),dept_idbigint,dept_namevarchar(50));createtabledept(idbigint,dept_namevarchar(50));关联更新updateuser_info usetdept_named.dept_namefromdept dwhereu.dept_idd.id执行逻辑 先查询select * from user_info u join dept d on u.dept_id d.id;得到结果再把d.dept_name更新到u.dept_name子查询聚合后更新updatesale_order ssettotal_amountt.amountfrom(selectorder_id,sum(qty*price)asamountfromorder_itemgroupbyorder_id)twheres.idt.order_id;使用场景更新库存余额更新总金额更新已发数量更新已入库数量订单明细sale_order主表;order_item多字段同时更新updatesale_order s-- 常见用法settotal_qtyt.qty,total_amountt.amount,item_countt.cntfrom(selectorder_id,sum(qty)asqty,sum(amount)asamount,count(*)ascntfromorder_itemgroupbyorder_id)twheres.idt.order_id;使用WITH UPDATE FROMwithtmpas(-- 进阶用法selectorder_id,sum(amount)astotal_amountfromorder_itemgroupbyorder_id)updatesale_order ssettotal_amountt.total_amountfromtmp twheres.idt.order_id;递归结果更新withrecursive treeas(selectid,parent_id,1aslevelfromdeptwhereparent_idisnullunionallselectd.id,d.parent_id,t.level1fromdept djointree tond.parent_idt.id)updatedept dsetdept_levelt.levelfromtree twhered.idt.id;批量CASE WHEN更新 vsUPDATE FROMupdatetestsetstatuscasewhenid1thenAwhenid2thenBend;updatetest t-- 推荐下面这种写法适用于 Java 批量更新、导入 Excel 或临时修正数据。据setstatusx.statusfrom(values(1,A),(2,B))asx(id,status)wheret.idx.id;UPDATE FROM EXISTSupdatestock ssetstatusEMPTYfrom(selectitem_idfromstockgroupbyitem_idhavingsum(qty)0)twheres.item_idt.item_id;避免重复 提高安全性最危险的问题多对一更新问题updateasetnameb.namefrombwherea.idb.id;-- 正确做法 from 后面的结果唯一-- group byselectid,max(name)groupbyid-- distinct onselectdistincton(id)id,namefromborderbyid,create_timedesc总结场景推荐写法核心要点普通关联更新UPDATE ... FROM ... WHERE确保关联键唯一避免多对一覆盖聚合统计更新子查询GROUP BYUPDATE FROM企业系统中最常见的模式多字段同时更新子查询返回多个聚合字段一次扫描多处更新复杂逻辑更新WITHCTE UPDATE FROM逻辑清晰易于维护批量定点更新VALUES()构造临时表Java 批量导入、Excel 修正数据的最佳实践安全检查加EXISTS或提前去重防止误更新提高健壮性注意事项⚠️关联键的唯一性当FROM子句中的来源表有多条记录匹配目标表的一条记录时最终更新结果是不确定的取决于最后匹配到的哪一行。务必使用GROUP BY、DISTINCT ON或窗口函数确保来源表的关联键唯一。适用场景数据同步从维度表更新事实表的冗余字段统计回写订单汇总金额写回主表、库存余额汇总批量修正Excel 导入临时数据批量更新递归更新组织架构层级、BOM 成本分摊UPDATE FROM是相对高级更新技巧它能让你的代码更高效、更简洁。