W
watter
Unregistered / Unconfirmed
GUEST, unregistred user!
/*数据表有一些冗余的数据行,表现为某两个字段相同,而其他字段不同,现在需要删除多余的行
只保留一行,希望用游标操作实现,但是无论怎样定义游标均提示游标为只读。SQL语句如下:*/
If Exists(Select name From SysObjects
Where name='TestTable' and xtype='U')
Drop Table TestTable
GO
Create Table TestTable
( ID int IDENTITY (1, 1) NOT NULL ,
Col1 Char(10),
Col2 Char(10),
Col3 Char(10)
)
Insert into TestTable Values('11','11','11')
Insert into TestTable Values('11','11','22')
Insert into TestTable Values('11','22','11')
Insert into TestTable Values('11','22','11')
Insert into TestTable Values('22','11','33')
Select * From TestTable
Go
/*************************************************************/
Print '---------- Cursor Operate Begin ----------'
DECLARE
@Col10 char(10), @Col20 char(10),
@Col11 char(10), @Col21 char(10)
DECLARE TestCur CURSOR Optimistic --这里试验了所有的选项均不成功
FOR SELECT Col1, Col2
FROM TestTable ORDER BY Col1, Col2
-- FOR Update
--如果使用FOR Update,提示:
--Server: Msg 16929, Level 16, State 4, Line 7
--The cursor is READ ONLY.
OPEN TestCur
FETCH NEXT FROM TestCur INTO @Col10, @Col20
FETCH NEXT FROM TestCur INTO @Col11, @Col21
WHILE (@@FETCH_Status=0)
BEGIN
IF ((@Col10=@Col11) and (@Col20=@Col21))
Begin
Print 'Col1: '+ @Col10 + ', Col2: ' + @Col20
DELETE FROM TestTable WHERE CURRENT OF TestCur
-- Update TestTable Set Col2='aa' Where Current Of testcur
--系统提示:
--Server: Msg 16929, Level 16, State 1, Line 19
--The cursor is READ ONLY.
--The statement has been terminated.
FETCH NEXT FROM TestCur INTO @Col11, @Col21
End
Else
Begin
Set @Col10=@Col11
Set @Col20=@Col21
FETCH NEXT FROM TestCur INTO @Col11, @Col21
End
END
CLOSE TestCur
DEALLOCATE TestCur
Print '---------- Cursor Operate Finish ----------'
Go
/**********************************************************/
Select * from TestTable
Drop table TestTable
只保留一行,希望用游标操作实现,但是无论怎样定义游标均提示游标为只读。SQL语句如下:*/
If Exists(Select name From SysObjects
Where name='TestTable' and xtype='U')
Drop Table TestTable
GO
Create Table TestTable
( ID int IDENTITY (1, 1) NOT NULL ,
Col1 Char(10),
Col2 Char(10),
Col3 Char(10)
)
Insert into TestTable Values('11','11','11')
Insert into TestTable Values('11','11','22')
Insert into TestTable Values('11','22','11')
Insert into TestTable Values('11','22','11')
Insert into TestTable Values('22','11','33')
Select * From TestTable
Go
/*************************************************************/
Print '---------- Cursor Operate Begin ----------'
DECLARE
@Col10 char(10), @Col20 char(10),
@Col11 char(10), @Col21 char(10)
DECLARE TestCur CURSOR Optimistic --这里试验了所有的选项均不成功
FOR SELECT Col1, Col2
FROM TestTable ORDER BY Col1, Col2
-- FOR Update
--如果使用FOR Update,提示:
--Server: Msg 16929, Level 16, State 4, Line 7
--The cursor is READ ONLY.
OPEN TestCur
FETCH NEXT FROM TestCur INTO @Col10, @Col20
FETCH NEXT FROM TestCur INTO @Col11, @Col21
WHILE (@@FETCH_Status=0)
BEGIN
IF ((@Col10=@Col11) and (@Col20=@Col21))
Begin
Print 'Col1: '+ @Col10 + ', Col2: ' + @Col20
DELETE FROM TestTable WHERE CURRENT OF TestCur
-- Update TestTable Set Col2='aa' Where Current Of testcur
--系统提示:
--Server: Msg 16929, Level 16, State 1, Line 19
--The cursor is READ ONLY.
--The statement has been terminated.
FETCH NEXT FROM TestCur INTO @Col11, @Col21
End
Else
Begin
Set @Col10=@Col11
Set @Col20=@Col21
FETCH NEXT FROM TestCur INTO @Col11, @Col21
End
END
CLOSE TestCur
DEALLOCATE TestCur
Print '---------- Cursor Operate Finish ----------'
Go
/**********************************************************/
Select * from TestTable
Drop table TestTable