SQLCookBook第四章学习日记14

4.9当相应行存在时更新

问题：
仅当另一个表中相应的行存在时，更新某表中的一些行。例如，如果表emp_bonus中存在某位员工，则要将该员工的工资增加20%（在表emp中）。

解决方案：
为了可以将符合条件的员工工资增加20%，可以在update语句的where子句中使用子查询，用以找出哪些员工同时存在于表emp和emp_bonus中

update emp set sal = sal*1.20
    where empno in (select empno from emp_bonus)

讨论：
子查询返回的结果集确定了在表emp中哪些行可以被更新。谓词in用来检验emp中的empno值是否包含在由子查询返回的empno值列表当中，在此列表中时，相应的sal值被更新。

还可以使用exists子句来替代in：

update emp
    set sal = sal * 1.20
where exists (
    select null from emp_bonus 
        where emp.empno = emp_bonus.empno
)  

读者可能会奇怪在exists子查询中select列表中的NULL值，不要惊讶，NULL值对更新操作没有任何不利影响，我认为这样既增加了该语句的可读性，而且还强调了一个事实：与使用in操作符的查询不同，使用exists的解决方案中，由子查询的where子句决定要更新哪些行，而不是由子查询的select列表中的值决定

4.10用其他表中的值更新

问题：

要用一个表中的值来更新另外一个表中的行。例如，在表new_sal中保存着某个特定员工的新工资.

在表new_sal中，deptno为关键字。要用表new_sal中的值更新表emp中相应员工的工资，条件是emp.deptno与new_sal.deptno相等，将匹配记录的emp.sal更新为new_sal.sal，将emp.comm更新为new_sal.sal的50%。

解决方案：
在表new_sal和emp之间做链接，用来查找comm新值并带给update语句。像这样通过关联子查询进行更新的情况十分常见；另一种方法是创建视图（创建视图或内联视图，视数据库支持而定），然后更新这个视图。

DB2 and MySQL
使用相关的子查询来设置表emp中的sal和comm值，同样使用相关的子查询判别表emp中哪些行需要被更新：

update emp e 
    set (e.sal,e.comm)  = (select ns.sal,ns.sal/2 from new_sal ns 
                                    where ns.deptno = e.deptno)
where exists(select null from new_sal ns 
            where ns.deptno = e.deptno)

Oracle
DB2解决方案的方法也适用，但是，Oracle中还可以选择使用内联视图来进行更新：

update (
    select e.sal as emp_sal, e.comm as em_comm,
            ns.sal as ns_sal, ns.sal/2 as ns_comm
    from emp e. new_Sal ns
    where e.deptno = ns.deptno
) set emp_sal = ns_sal, emp_comm = ns_comm

PostgreSQL
DB2的解决方案的方法也适用，但PostgreSQL中还有另一种说法（更方便），就是在update语句中直接使用联接：

update emp 
    set sal = ns.sal,
        comm = ns.sal/2
    from new_sal ns
where ns.deptno = emp.deptno

SQL Server
DB2解决方案的方法也适用，但SQL Server还有一种办法（类似于PostgreSQL解决方案）就是在update语句中直接使用联接：

udpate e
    set e.sal = ns.sal
        e.comm = ns.sal/2
    from emp e,
        new_sal ns
where ns.deptno e.deptno

讨论：

在讨论这些不同的解决方案之前，先要提几个有关实用查询进行更新的重要方面。关联子查询中的where子句与更新语句的where子句是不同的。看一下问题部分的update语句，在表emp和new _sal之间按deptno进行联接，其结果行返回到update语句的set子句中，对于deptno为10的员工，因为在表new_sal 中存在匹配的deptno，故会返回有效值。但是，对于其他部门的员工来说结果又是如何呢？new_sal表中没有其他的部门，所以对于在deptno为20和30的员工，他们的sal和comm将设为空，除非通过limit，top或其他由供应商支持的机制来限制结果集中的数目，在sql中，只有一种方法可以限制从表中返回的行数，那就是使用where子句，要正确的执行update语句，对要更新的表使用where子句，同时，在关联子查询中也使用where子句。
DB2和MySQL
如果不需要更新表emp中的所有行，记住一定要在update语句中的where子句中包含关联子查询。仅在set子句中执行联接（关联子查询）是不够的。通过在update语句的where子句，可以确保在表emp中，只有deptno与表new_sal匹配的行被更新。此法则通常对于所有的RDBMS都有效。
Oracle
Oracle的解决方案使用更新联接视图，实际上是用等价值联接来判别对哪些行进行更新。单独执行一下查询就可以确定将更新哪些行。要成功的使用这种类型的update语句，首先必须理解键值保留表的概念。表new_sal的deptno列是该表的主关键字，这样，它的值在改变中部高是唯一的，然而在表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

如果要使Oracle能够更新此联接，联接中的一个表必须为键值保留表，意思是如果他的值在结果集中不唯一，至少这些值在来源表中是唯一的。本例中，表new_sal 的deptno 为关键字，表示该字段值在表中是唯一的。因为它在该表中唯一，所以虽然他在结果集中多次串，也认为是键值保留的，这样，更新就能成功的完成。

PostgreSQL和SQL Server
这两种平台上的该语法稍有不同，然而所使用的技巧是一样的，在update语句中直接做联接非常方便。因为指定了更新的目标表（update关键字后列出的表），就不会混淆哪个表中的行要被修改。另外，因为在更新中使用了联接（因为有显式的where子句），可以避免关联子查询更新编码的错误；还有们如果在这里遗漏了联接，显然表明此语句有问题。