34,838
社区成员




UPDATE TblPDataHead
SET case when Chid=@Chid then Chid=@Chid + 1
when Chid=@Chid+1 then @Chid
else Chid end
WHERE LANCfg=@LANCfg and chid between @chid and @chid + 1
create table ttt1 (chid int,name varchar(10))
insert into ttt1 select 5,'aa'
insert into ttt1 select 6,'bb'
declare @chid int
set @chid=5
update ttt1 set chid=case when a.chid=@chid then @chid+1 when a.chid=@chid+1 then @chid end
from ttt1 a
select * from ttt1
chid name
6 aa
5 bb
declare @t table(name char(1),orderflag int)
insert @t select 'd',1
insert @t select 'c',2
insert @t select 'b',3
begin
declare @i int
set @i = 1
update @t set orderflag = @i where orderflag = @i + 1
update @t set orderflag = @i+1 where orderflag = @i
end
select * from @t
/*
name orderflag
---- -----------
d 2
c 2
b 3
(所影响的行数为 3 行)
*/
delete from @t
insert @t select 'd',1
insert @t select 'c',2
insert @t select 'b',3
begin
update @t
set orderflag = case when orderflag = @i then @i + 1 when orderflag = @i+1 then @i else orderflag end
end
select * from @t
/*
name orderflag
---- -----------
d 2
c 1
b 3
(所影响的行数为 3 行)
*/
id child lancfg
1 1 1
2 2 1
==
set @child = 1
UPDATE TblPDataHead SET Chid=@Chid WHERE Chid=@Chid+1 AND LANCfg=@LANCfg
COMMIT
---
id child lancfg
1 1 1
2 1 1
UPDATE TblPDataHead SET Chid=@Chid+1 WHERE Chid=@Chid AND LANCfg=@LANCfg
COMMIT
id child lancfg
1 2 1
2 2 1
----