【SQL】update关联更新

是这样的，我们系统里有个员工表CCT_SYS_STAFF，里面有个字段是保存的员工的密码。

一般情况下，这个密码员工是不记得的，因为应用登录时是通过另外一套认证系统来做鉴权，然后跳转到我们系统。

但是如果那套鉴权系统出现问题，那么就需要员工从我们这个表里做身份鉴权，此时大部分员工忘记了自己的密码，那么我们需要为其设置统一密码。

在应急登录之后，我们又需要将原来的密码给还原。所以我们在重置统一密码之前，需要将原表备份。后面直接利用备份表来恢复密码。

这很明显就是一个update的关联更新操作。

如：备份表

CCT_SYS_STAFF_bak0925

要更新的原表：

CCT_SYS_STAFF

更新字段：staff_pwd

第一种方法：

update CCT_SYS_STAFF t1
   set t1.staff_pwd =
       (select t2.staff_pwd
          from CCT_SYS_STAFF_bak0925 t2
         where t1.staff_id = t2.staff_id
         and t1.staff_pwd<>t2.staff_pwd)
 where exists (select 1
          from CCT_SYS_STAFF_bak0925 t2
         where t2.staff_id = t1.staff_id
         and t1.staff_pwd<>t2.staff_pwd)

要执行速度快，必须加上两个表在staff_id上的索引（有个uk或者pk更好）。

第二种方法：

update (select t1.staff_id, t1.staff_pwd as pwd, t2.staff_pwd as pwd_new
          from CCT_SYS_STAFF t1, CCT_SYS_STAFF_bak0925 t2
         where t1.staff_id = t2.staff_id
           and t1.staff_pwd <> t2.staff_pwd) t3
   set t3.pwd = t3.pwd_new
ORA-01779: 无法修改与非键值保存表对应的列

SQL> 

报错01779，表示在staff_id列上必须有一个uk或者pk，普通索引还不行。

分别添加唯一键约束：

alter table CCT_SYS_STAFF
  add constraint UK_STAFF_ID unique (STAFF_ID)
  using index 
  tablespace USERS;
  
alter table CCT_SYS_STAFF_BAK0925
  add constraint UK_STAFF_ID_BAK0925 unique (STAFF_ID)
  using index 
  tablespace USERS;

再尝试更新：

SQL> update (select t1.staff_id, t1.staff_pwd as pwd, t2.staff_pwd as pwd_new
  2            from CCT_SYS_STAFF t1, CCT_SYS_STAFF_bak0925 t2
  3           where t1.staff_id = t2.staff_id
  4             and t1.staff_pwd <> t2.staff_pwd) t3
  5     set t3.pwd = t3.pwd_new;

30000 rows updated


SQL> 

成功执行更新操作。