UPDATE、ROW_NUMBER 与 MERGE

环境如下，建一测试表
CREATE TABLE emp2 AS SELECT * FROM emp;
alter table emp2 add constraints pk_emp primary key(empno);

有人想达到如下目的
/*UPDATE emp2 SET empno = 201311070000000000 + ROW_NUMBER() OVER(ORDER BY job,hiredate)*/

于是用update写法如下

SQL> UPDATE emp2 a
  2     SET a.empno =
  3         (SELECT 201311070000000000 + row_number() over(ORDER BY b.job, b.hiredate) empno
  4            FROM emp2 b
  5           WHERE b.empno = a.empno);
UPDATE emp2 a
   SET a.empno =
       (SELECT 201311070000000000 + row_number() over(ORDER BY b.job, b.hiredate) empno
          FROM emp2 b
         WHERE b.empno = a.empno)
ORA-00001: 违反唯一约束条件 (TEST.PK_EMP)

上述会报错，是因为条件 （WHERE b.empno = a.empno) 那么每行生成的no都是一样的。
于是用merge改写如下，成功。

MERGE INTO emp2 a
USING (SELECT b.rowid AS rid,
              201311070000000000 + row_number() over(ORDER BY b.job, b.hiredate) AS empno
         FROM emp2 b) b
ON (b.rid = a.rowid)
WHEN MATCHED THEN
  UPDATE SET a.empno = b.empno;