利用子查询更新字段

update sq_forum_reply s set floor = (
select row_number() over(partition by thread_id order by gmt_create)
from sq_forum_reply t
where s.id = t.id
and t.thread_id =20013990
and floor is null
) where thread_id=20013990 and floor is null;

查看执行计划发现，原因是子查询根据要更新的当前行的id去查询匹配记录，这样子查询每次只能查到一条记录，所以排序始终等于1。

修改语句后：

update sq_forum_reply s set floor = (
select rn from (
select id,row_number() over(partition by thread_id order by gmt_create) rn
from sq_forum_reply t
where t.thread_id = 20013990 and floor is null
) where s.id = id
) where thread_id=20013990 and floor is null ;

sql> select id,floor from sq_forum_reply where thread_id=20013990;

ID FLOOR
———- ———-
20004120 1
20004121 2
20004122 3

所以在写这类sql时，一定要确保子查询的结果集和要更新的结果集一致。

http://www.alidba.net/index.php/archives/50/trackback