求一条sql更新语句
ft ftn ftname old
1 8 d 1
1 9 e 1
2 10 f 2
2 11 g 2
3 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
需要更改ft为新的ftn,更新结果为
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
ft ftn ftname old
1 8 d 1
1 9 e 1
2 10 f 2
2 11 g 2
3 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
需要更改ft为新的ftn,更新结果为
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
ft ftn ftname old
0 1 a 1
0 2 b 2
0 3 c 3
1 8 d 1
1 9 e 1
2 10 f 2
2 11 g 2
3 12 h 3
ftn是自增字段删除记录后再添加变成
ft ftn ftname old
1 8 d 1
1 9 e 1
2 10 f 2
2 11 g 2
3 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3现在需要更改ft为新的ftn,更新结果为
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
insert into #tb
select 1,8,'d',1
union all select 1,9,'e',1
union all select 2,10,'f',2
union all select 2,11,'g',2
union all select 3,12,'h',3
union all select 0,13,'a',1
union all select 0,14,'b',2
union all select 0,15,'c',3update a
set ft=b.ftn
from #tb a
inner join (select old,MAX(ftn) as ftn from #tb group by old)b
on a.ft=b.old
select * from #tb/*
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
*/
go
create table [TB] (ft int,ftn int,ftname nvarchar(2),old int)
insert into [TB]
select 1,8,'d',1 union all
select 1,9,'e',1 union all
select 2,10,'f',2 union all
select 2,11,'g',2 union all
select 3,12,'h',3 union all
select 0,13,'a',1 union all
select 0,14,'b',2 union all
select 0,15,'c',3select * from [TB]BEGIN TRAN D1UPDATE dbo.TB
SET FT =T.ftn
FROM TB
INNER JOIN TB T ON TB.ft=T.old AND T.ft = 0COMMIT TRAN D1/*
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3*/
AS
(
select 1,8,'d',1 union all
select 1,9,'e',1 union all
select 2,10,'f',2 union all
select 2,11,'g',2 union all
select 3,12,'h',3 union all
select 0,13,'a',1 union all
select 0,14,'b',2 union all
select 0,15,'c',3
) SELECT *
INTO #T
FROM cte
UPDATE a
SET a.ft=b.ftn
FROM #T a JOIN #T b ON b.ft=0 AND a.ft=b.old SELECT * FROM #T
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
insert into test values (1,8,'d',1)
insert into test values (1,9,'e',1)
insert into test values (2,10,'f',2)
insert into test values (2,11,'g',2)
insert into test values (3,12,'h',3)
insert into test values (0,13,'a',1)
insert into test values (0,14,'b',2)
insert into test values (0,15,'c',3)
UPDATE test
SET ft=a.ftn
FROM test JOIN test a
ON a.ft=0 AND test.ft=a.old
select * from test
求一条sql更新语句
ft ftn ftname old
1 8 d 1
1 9 e 1
2 10 f 2
2 11 g 2
3 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
需要更改ft为新的ftn,更新结果为
ft ftn ftname old
13 8 d 1
13 9 e 1
14 10 f 2
14 11 g 2
15 12 h 3
0 13 a 1
0 14 b 2
0 15 c 3
*/create table tab(ft int ,ftn int ,ftname char(1),old int)
insert into tab(ft,ftn,ftname,old)
select 1,8,'d', 1 union all
select 1,9,'e', 1 union all
select 2,10,'f',2 union all
select 2,11,'g',2 union all
select 3,12,'h',3 union all
select 0,13,'a',1 union all
select 0,14,'b',2 union all
select 0,15,'c',3 update tab set ft = t2.ftn
from tab t1 inner join (select * from tab where ft =0) t2
on t1.ft = t2.oldselect * from tab