34,587
社区成员
发帖
与我相关
我的任务
分享
declare @maxid int
select @maxid = max(index) from tablename
update tablename
set index = @maxid
where index=1
update tablename
set index = index+1
where index<>@maxid
--看下以下例子,可能有帮助
/*表 table_1
列 id(int) name(varcher) time(datetime) 有10行数据, 2到4行 和 5到7行的数据调换位置并且 时间(time)行 不变
*/
update table_1 set name=a.name,time=a.time
from
(
select id+3,name,time from table_1 where id between 2 and 4
union all
select id-3,name,time from table_1 where id between 5 and 7
) a
where table_1.id=a.id
--或使用
update table_1 set id=(case when id between 2 and 4 then id+3 when id between 5 and 7 then id-3 else id end)
update a
set a.index=b.index
from tb a,tb b
where a.id=b.id-1
and a.id=xxx
--创建下移的过程
create procedure down(@id int)
as
begin
declare @down_id int
declare @temp_id int
set @down_id = @id +1
select @temp_id=max(index)+1 from tablename
update tablename
set index = @temp_id
where index=@down_id
update tablename
set index = @down_id
where index= @id
update tablename
set index = @id
where index= @temp_id
end
--执行过程 将第三行向下移
exec down 3
--创建上移的过程
create procedure up(@id int)
as
begin
declare @up_id int
declare @temp_id int
set @up_id = @id -1
select @temp_id=max(index)+1 from tablename
update tablename
set index = @temp_id
where index=@up_id
update tablename
set index = @up_id
where index= @id
update tablename
set index = @id
where index= @temp_id
end
--执行过程 将第三行向上移
exec up 3
--传入Index=3,使其和上面Index=2的记录交换Index值
update tb set Index=a.Index
from
(
select Index-1 as index from tb where Index=3
union all
select 3 as Index from tb where Index=3-1
) a
where tb.Index=a.Index