--想用游标把1、2、3.....10更新到数据表Mamember,里的字段txnid
declare @i int,@j int
declare mm cursor for select txnid from Mamember
set @i=1
open mm
FETCH NEXT FROM mm into @jwhile(@@fetch_status =0 )
begin
print @i
update Mamember set txnid=@i
set @i=@i+1
FETCH NEXT FROM mm into @j
endclose mm
deallocate mm相当于产生自动编号的效果
txnid
---------------
1
2
3
.
.
10
--请教高手,上述哪里出问题了?
declare @i int,@j int
declare mm cursor for select txnid from Mamember
set @i=1
open mm
FETCH NEXT FROM mm into @jwhile(@@fetch_status =0 )
begin
print @i
update Mamember set txnid=@i
set @i=@i+1
FETCH NEXT FROM mm into @j
endclose mm
deallocate mm相当于产生自动编号的效果
txnid
---------------
1
2
3
.
.
10
--请教高手,上述哪里出问题了?
-- Author : htl258(Tony)
-- Date : 2010-05-03 22:17:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [Mamember]
IF OBJECT_ID('[Mamember]') IS NOT NULL
DROP TABLE [Mamember]
GO
CREATE TABLE [Mamember] ([col] [nvarchar](10),[txnid] sql_variant)
INSERT INTO [Mamember]
SELECT 'a',null UNION ALL
SELECT 'b',null UNION ALL
SELECT 'c',null UNION ALL
SELECT 'd',null UNION ALL
SELECT 'e',null UNION ALL
SELECT 'f',null UNION ALL
SELECT 'g',null UNION ALL
SELECT 'h',null UNION ALL
SELECT 'i',null UNION ALL
SELECT 'j',null UNION ALL
SELECT 'k',null--SELECT * FROM [Mamember]-->SQL查询如下:
declare @i int
update [Mamember] set [txnid]=@i,@i=ISNULL(@i,0)+1select * from [Mamember]
/*(11 行受影响)(11 行受影响)
col txnid
---------- ---------------------
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9
j 10
k 11(11 行受影响)*/不用游标,这样就可以了。
-- Author : htl258(Tony)
-- Date : 2010-05-03 22:17:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [Mamember]
IF OBJECT_ID('[Mamember]') IS NOT NULL
DROP TABLE [Mamember]
GO
CREATE TABLE [Mamember] ([mm] [nvarchar](10),[txnid] int)
INSERT INTO [Mamember]
SELECT 'a',null UNION ALL
SELECT 'b',null UNION ALL
SELECT 'c',null UNION ALL
SELECT 'd',null UNION ALL
SELECT 'e',null UNION ALL
SELECT 'f',null UNION ALL
SELECT 'g',null UNION ALL
SELECT 'h',null UNION ALL
SELECT 'i',null UNION ALL
SELECT 'j',null UNION ALL
SELECT 'k',null--SELECT * FROM [Mamember]-->SQL查询如下:
declare @i int,@j int
declare mm cursor for
select txnid from Mamember
set @i=1
open mm
FETCH NEXT FROM mm into @j
while(@@fetch_status =0 )
begin
print @i
UPDATE [Mamember] SET [txnid]=@i WHERE CURRENT of mm
set @i=@i+1
FETCH NEXT FROM mm into @j
end
close mm
deallocate mmselect * from [Mamember]
/*
mm txnid
---------- -----------
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9
j 10
k 11(11 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-05-03 22:17:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [Mamember]
IF OBJECT_ID('[Mamember]') IS NOT NULL
DROP TABLE [Mamember]
GO
CREATE TABLE [Mamember] ([mm] [nvarchar](10),[txnid] int)
INSERT INTO [Mamember]
SELECT 'a',null UNION ALL
SELECT 'b',null UNION ALL
SELECT 'c',null UNION ALL
SELECT 'd',null UNION ALL
SELECT 'e',null UNION ALL
SELECT 'f',null UNION ALL
SELECT 'g',null UNION ALL
SELECT 'h',null UNION ALL
SELECT 'i',null UNION ALL
SELECT 'j',null UNION ALL
SELECT 'k',null--SELECT * FROM [Mamember]-->SQL查询如下:
declare @i int,@j [nvarchar](10)
declare mm cursor for
select [mm] from Mamember
set @i=1
open mm
FETCH NEXT FROM mm into @j
while(@@fetch_status =0 )
begin
print @i
UPDATE [Mamember] SET [txnid]=@i WHERE mm=@j
set @i=@i+1
FETCH NEXT FROM mm into @j
end
close mm
deallocate mmselect * from [Mamember]
/*
mm txnid
---------- -----------
a 1
b 2
c 3
d 4
e 5
f 6
g 7
h 8
i 9
j 10
k 11(11 行受影响)
*/这样也可以。
IF object_id('t_student')IS NOT NULL DROP TABLE t_student
CREATE TABLE t_student
(
sortid INT,
name VARCHAR(30)
)INSERT INTO t_student VALUES (0,'111'),
(0,'2222'),
(0,'1113'),
(0,'1114'),
(0,'1115'),
(0,'1116'),
(0,'1117'),
(0,'1118'),
(0,'1119'),
(0,'1110'),
(0,'111222')SELECT * FROM t_student
DECLARE up_cursor CURSOR FOR
SELECT sortid FROM t_student FOR
UPDATE of sortid
OPEN up_cursorFETCH next FROM up_cursor
WHILE @@FETCH_STATUS =0
BEGIN
DECLARE @sortid INT
SET @sortid=(SELECT max(sortid)+1 FROM t_student )
UPDATE t_student
SET sortid=@sortid
WHERE CURRENT OF up_cursor
SET @sortid=@sortid+1
FETCH next FROM up_cursor
END CLOSE up_cursor
DEALLOCATE up_cursor
SELECT * FROM t_student