极客教程SQL 使用另一个表的数据更新记录，你想使用另一个表的值来更新当前的表。例如，现在有一个 NEW_SAL 表，存储了部分员工调整后的工资。NEW_SAL 表的数据如下。
SQL 使用另一个表的数据更新记录 问题描述
你想使用另一个表的值来更新当前的表。例如，现在有一个 NEW_SAL 表，存储了部分员工调整后的工资。NEW_SAL 表的数据如下。
select *
from new_sal
DEPTNO SAL
------ ----------
10 4000
DEPTNO 列是 NEW_SAL 表的主键。你希望使用 NEW_SAL 表的数据来更新 EMP 表中部分员工的工资和业务提成。如果 EMP 表中的 DEPTNO 列和 NEW_SAL 表中的 DEPTNO 列相匹配，则将 EMP 表中的 SAL 列更新为 NEW_SAL 表中的 SAL 列，EMP 表中的 COMM 列更新为 NEW_SAL 表中 SAL 列的 50%。EMP 表的全部数据如下所示。
select deptno,ename,sal,comm
from emp
order by 1
DEPTNO ENAME SAL COMM
------ ---------- ---------- ----------
10 CLARK 2450
10 KING 5000
10 MILLER 1300
20 SMITH 800
20 ADAMS 1100
20 FORD 3000
20 SCOTT 3000
20 JONES 2975
30 ALLEN 1600 300
30 BLAKE 2850
30 MARTIN 1250 1400
30 JAMES 950
30 TURNER 1500 0
30 WARD 1250 500
SQL 使用另一个表的数据更新记录 解决方案
把 NEW_SAL 表和 EMP 表连接起来，为 UPDATE 语句找出新的 COMM 值。正如本实例所示，在 UPDATE 语句里使用关联子查询是常用做法。另一种方法是创建一个视图（传统视图或者内嵌视图均可，这取决于数据库是否支持），然后更新该视图即可。
DB2 和 MySQL
使用关联查询来更新 EMP 表的 SAL 列和 COMM 列，同时也要使用另一个关联子查询来决定 EMP 表里有哪些行应该被更新。
1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2
2 from new_sal ns
3 where ns.deptno=e.deptno)
4 where exists ( select null
5 from new_sal ns
6 where ns.deptno = e.deptno )
Oracle
DB2 的解决方案当然也适用于 Oracle，不过还有另外一种方法，即更新内嵌视图。
1 update (
2 select e.sal as emp_sal, e.comm as emp_comm,
3 ns.sal as ns_sal, ns.sal/2 as ns_comm
4 from emp e, new_sal ns
5 where e.deptno = ns.deptno
6 ) set emp_sal = ns_sal, emp_comm = ns_comm
PostgreSQL
DB2 的解决方案同样适用于 PostgreSQL，也可以在 UPDATE 语句里直接进行连接查询（非常方便）。
1 update emp
2 set sal = ns.sal,
3 comm = ns.sal/2
4 from new_sal ns
5 where ns.deptno = emp.deptno
SQL Server
DB2 的解决方案同样适用于 SQL Server，也可以在 UPDATE 语句里直接进行连接查询（类似于 PostgreSQL 解决方案）。
1 update e
2 set e.sal = ns.sal,
3 e.comm = ns.sal/2
4 from emp e,
5 new_sal ns
6 where ns.deptno = e.deptno
SQL 使用另一个表的数据更新记录 扩展知识
在讨论各种解决方案之前，我想先说一下在 UPDATE 语句里使用 SELECT 查询提供新值的问题。在 UPDATE 语句的关联子查询里使用 WHERE 子句不同于针对需要更新的表所使用的 WHERE 子句。如果你看一下“解决方案”部分的 UPDATE 语句就会明白，EMP 表和 NEW_SAL 表基于 DEPTNO 列连接之后，把查询结果传递给了 UPDATE 语句的 SET 子句。对于部门编号为 10 的员工而言，会将对应的有效值传递给 SET 子句，因为在 NEW_SAL 表里有与之相匹配的 DEPTNO。但是对其他部门的员工而言，又当如何呢？ NEW_SAL 表里没有其他部门的数据，因此对于部门编号为 20 和 30 的员工来说，SAL 列和 COMM 列会变为 Null。除非使用 LIMIT、TOP 或者其他由数据库提供的限制结果集行数的机制，否则在 SQL 数据库里唯一能限制行数的办法就是使用 WHERE 子句。因此，为了正确地执行 UPDATE，有时候要针对需要更新的表使用 WHERE 子句，有时候却要在关联子查询里使用 WHERE 子句。
DB2 和 MySQL
为了确保不会误改 EMP 表的全部行，要记得在 UPDATE 语句的 WHERE 子句里使用关联子查询。因为仅仅在 SET 子句里执行连接查询（关联子查询）是不够的。UPDATE 语句的 WHERE 子句能够确保只更新 EMP 表中那些与 NEW_SAL 表的 DEPTNO 列相匹配的行。这适用于所有的关系数据库管理系统。
Oracle
Oracle 解决方案使用了可更新的连接视图，由相等连接查询来决定哪些行将被更新。我们可以单独执行该查询语句来确认哪些行会被更新，也可以单独执行该内嵌视图的查询语句来确认哪些行将被更新。若想正确地使用这种类型的 UPDATE，你必须先理解“键值保持”（key-preservation）。DEPTNO 列是 NEW_SAL 表的主键，所以它的值是唯一的。当 EMP 表和 NEW_SAL 表做连接查询时，NEW_SAL 表的 DEPTNO 列在结果集里却不是唯一的，如下所示。
select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptno
from emp e, new_sal ns
where e.deptno = ns.deptno
EMPNO E_DEPT SAL NS_DEPTNO
----- ---------- ---------- ----------
7782 10 4000 10
7839 10 4000 10
7934 10 4000 10
为了让 Oracle 能够通过上述连接查询更新基础表，其中一个基础表必须符合键值保持的要求。也就是说，如果它的值在连接查询的结果集里不是唯一的，那么至少在基础表里是唯一的。在本例中，NEW_SAL 表的主键是 DEPTNO 列，因而符合唯一性的要求。既然 DEPTNO 列在基础表里是唯一的，那么它就可以多次出现在连接查询的结果集里，并被判定为符合键值保持的要求，更新操作也因此得以成功执行。
PostgreSQL 和 SQL Server
对于这两个数据库而言，语法略有不同，但方法是一样的。支持在 UPDATE 语句里直接进行连接查询自是方便之极。由于指明了要更新哪个表（通过在 UPDATE 关键字之后给出表的名字），数据库系统也就会知道要修改哪个表。除此之外，由于在更新操作里使用了连接查询（因为显式地使用了 WHERE 子句），我们得以避免一些关联子查询的陷阱。尤其是如果不小心漏掉了此处的连接查询，那么就能很容易发现问题。