比如我有一个A表,只有一个字段num,
里面有100条记录都是 AB001CD
我现在要修改这100条记录,结果要变成
AB001CD
AB002CD
AB003CD
……
AB099CD
AB100CD
该怎样实现呢?
里面有100条记录都是 AB001CD
我现在要修改这100条记录,结果要变成
AB001CD
AB002CD
AB003CD
……
AB099CD
AB100CD
该怎样实现呢?
set @i= 0
update ta
set num = left(@col,2)+right('000'+ltrim(@i),3)+right(col,2),@i = @i + 1
-- Author: happyflystone
-- Date : 2009-03-30 16:54:29
-- Ver: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL
DROP TABLE ta
Go
CREATE TABLE ta(num NVARCHAR(7))
Go
INSERT INTO ta
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD' UNION ALL
SELECT 'AB001CD'
GO
--Startdeclare @i int
set @i= 0
update ta
set num = left(num,2)+right('000'+ltrim(@i),3)+right(num,2),@i = @i + 1
SELECT
*
FROM
TA--Result:
/*num
-------
AB001CD
AB002CD
AB003CD
AB004CD
AB005CD
AB006CD
AB007CD
AB008CD
AB009CD
AB010CD
AB011CD
AB012CD
AB013CD
AB014CD
AB015CD
*/
--End
set @i=1
while @i<=100
begin
update A top 1
set A.num='AB00'+'@i'+'CD'
from A
where A.num='AB001CD'
set @i=@i+1
end
insert @t select 'AB001CD'
insert @t select 'AB001CD'
insert @t select 'AB001CD'declare @num int
update @t set num='AB'+right(1000+@num,3)+'CD',@num=isnull(@num+1,1)select * from @tnum
----------
AB001CD
AB002CD
AB003CD(3 行受影响)