--? DECLARE @test TABLE(kkey varchar(10),orderno int)INSERT INTO @test(kkey,orderno) select '1-1_',1 union all select '1-2_',2 union all select '1-3_',3 BEGIN TRANSACTION DELETE FROM @test WHERE orderno = 2 IF @@ERROR <> 0 ROLLBACK TRANSACTION UPDATE @test SET kkey='1-'+CAST(orderno-1 AS VARCHAR(2))+'_',orderno=orderno-1 WHERE orderno > 2 IF @@ERROR <> 0 ROLLBACK TRANSACTION COMMIT TRANSACTIONSELECT * FROM @test /* kkey orderno ---------- ----------- 1-1_ 1 1-2_ 2 */
DECLARE @test TABLE(kkey varchar(10),orderno int)INSERT INTO @test(kkey,orderno)
select '1-1_',1 union all
select '1-2_',2 union all
select '1-3_',3 BEGIN TRANSACTION
DELETE FROM @test WHERE orderno = 2
IF @@ERROR <> 0 ROLLBACK TRANSACTION
UPDATE @test SET kkey='1-'+CAST(orderno-1 AS VARCHAR(2))+'_',orderno=orderno-1 WHERE orderno > 2
IF @@ERROR <> 0 ROLLBACK TRANSACTION
COMMIT TRANSACTIONSELECT * FROM @test
/*
kkey orderno
---------- -----------
1-1_ 1
1-2_ 2
*/