row_number 函数和关联更新

生成测试数据，房间号数据如下：

CREATE TABLE hotel (floor_nbr,room_nbr) AS

SELECT 1,100 FROM DUAL UNION ALL

SELECT 1,100 FROM DUAL UNION ALL

SELECT 2,100 FROM DUAL UNION ALL

SELECT 2,100 FROM DUAL UNION ALL

SELECT 3,100 FROM DUAL;

里面的房间号是不对的，一般是101、102、201、202的形式。现要求重新生成房间号，可以用 row_number 函数来更新数据，重新生成房间号。

update hotel set room_nbr =  floor_nbr * 100 + row_number() over(partition by floor_nbr order by rownum);

ORA-30483: window 函数在此禁用

试试看“可更新VIEW”

update (select room_nbr,

               floor_nbr * 100 + row_number() over(partition by floor_nbr order by rowid) as new_nbr

          from hotel)

   set room_nbr = new_nbr;

ORA-01732: 此视图的数据操纵操作非法

update再嵌套一层倒是可以实现：

update hotel a

   set room_nbr = (select room_nbr

                     from (select floor_nbr * 100 + row_number() over(partition by floor_nbr order by rowid) as room_nbr

                             from hotel) b

                    where a.rowid = b.rowid);

select * from hotel;

试试 merge into 的方式，也可以：

merge into hotel a

using (select rowid as rid,floor_nbr * 100 + row_number() over(partition by floor_nbr order by rowid) as room_nbr

         from hotel) b

on (a.rowid = b.rid)

when matched then

  update set a.room_nbr = b.room_nbr;