怎样才能删除游标所指的行?DELETE FROM table_name WHERE CURRENT OF cursor_name不能用。(100分)

  • 主题发起人 主题发起人 watter
  • 开始时间 开始时间
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帮助中好像什么选项也不需要写的
 
好像是Order By 的缘故, 你把它去掉就应该可以了
 
Order by 不能用,如果必须排序,可以另建临时表,如下:
/*数据表有一些冗余的数据行,表现为某两个字段相同,而其他字段不同,现在需要删除多余的行
只保留一行,希望用游标操作实现,但是无论怎样定义游标均提示游标为只读。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','22','22')
Insert into TestTable Values('11','11','11')
Insert into TestTable Values('11','22','11')
Insert into TestTable Values('22','11','33')
If Exists(Select name From SysObjects
Where name='TestTable1' and xtype='U')
Drop Table TestTable1
Select * into TestTable1 from TestTable Order by Col1, Col2
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 Dynamic --这里试验了所有的选项均不成功
FOR SELECT Col1, Col2
FROM TestTable1-- 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 TestTable1 WHERE CURRENT OF TestCur
Update TestTable1 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
/**********************************************************/
Truncate Table TestTable
Insert TestTable(Col1, Col2,Col3) Select Col1, Col2,Col3 from TestTable1
Select * from TestTable
Drop table TestTable
Drop Table TestTable1
 
接受答案了.
 
后退
顶部