Archive for February 29th, 2008
利用子查询更新字段
Friday, February 29th, 2008
今天在做数据更新时，打算将某个帖子下面的所有回复排定楼层，测试发现结果不对。
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 [...]
