SQL操作行更新问题---使用游标

这张表格是要进行更行的表userData

USERID

1,2,3,4

4,5,6,7

下面这张表格是一张影射表，从userData表中找到与map_table相匹配的old_ID，并且用new_ID来更新响应的USERID

map_table

old_ID	new_ID
2	Y222
1	Y111
6	Y666
8	Y888
7	Y777
4	Y444
3	Y333
5	Y555

一开始考虑的时候是先把数据取出来，在CS代码中处理完以后再存放进去。但是这里遇到一个问题就是要一行一行地遍历userData表格，这就使得代码相当地难处理。既然是行处理，于是想到了用游标来解决这个问题。花了将近6个小时，终于把这个问题解决了，学到了不少东西，呵呵，贴上来分享。更新以后的userData表格如下：

userData

USERID

Y111,Y222,Y333,Y444

Y555,Y666,Y777,Y888

 

-- Updating the Rows through CURSOR --
DECLARE @userid VARCHAR( 200 ) , @new_ID VARCHAR( 200 )
-- 逗号的索引位置 --
DECLARE @index  int
-- 逗号 --
DECLARE @comma varchar( 3 )
-- 存放sec_usrdata表里的userid --
DECLARE @temp VARCHAR( 200 )
-- 保存要更新的数据 --
DECLARE @sum varchar( 200 )
DECLARE @new_ID_backup varchar( 200 )
-- 查询纪录的行数 --
DECLARE @rowCount  int
--  初始化 --
set  @comma  =   ' , '
set  @index  =   0
set  @sum  =   ''
set  @temp  =   ''
set  @new_ID_backup  =   ''
set  @rowCount  =- 1

-- 声明游标 --
DECLARE curr1 CURSOR 

FOR SELECT USERID from SEC_USRDATA  for  Update

OPEN curr1
FETCH next from curr1 into @userid

WHILE (@@FETCH_status  =   0 )
    
BEGIN
     -- 清空内容 --
     set  @sum  =   ''
     set  @rowCount =- 1
     set  @index  =  CHARINDEX(@comma,@userid, 0 )
     set  @temp  =  SUBSTRING(@userid, 0 ,@index)
    

    Select @new_ID = new_ID from Map_Table  where  old_id  =  @temp
    select @rowCount = count( * ) from Map_Table  where  old_id  =  @temp

     if (@rowCount  =   0 )
        begin
             set  @sum  =  @sum + @temp + @comma
            
        end
     if (@rowCount  >   0 )
        begin
             set  @sum  =  @sum + @new_ID + @comma
            
        end
    

     while (@index  <  datalength(@userid))
        begin
             set  @userid  =  SUBSTRING(@userid,@index + 1 ,datalength(@userid))
            
             set  @index  =  CHARINDEX(@comma,@userid, 0 )
            
             if (@index  =   0 )
            begin
                 set  @temp  =  SUBSTRING(@userid, 0 ,datalength(@userid) + 1 )
                
                Select @new_ID = new_ID from Map_Table  where  old_id  =  @temp
                select @rowCount = count( * ) from Map_Table  where  old_id  =  @temp
                 if (@rowCount  =   0 )
                    begin
                         set  @sum  =  @sum + @temp
                        print @sum
                    end
                 if (@rowCount  >   0 )
                    begin
                         set  @sum  =  @sum + @new_ID
                        print @sum
                    end
                 break
            end
             else
            begin
                 set  @temp  =  SUBSTRING(@userid, 0 ,@index)
                Select @new_ID = new_ID from Map_Table  where  old_id  =  @temp
                select @rowCount = count( * ) from Map_Table  where  old_id  =  @temp
                
                 if (@rowCount  =   0 )
                    begin
                         set  @sum  =  @sum + @temp + @comma
                        print @sum
                    end
                 if (@rowCount  >   0 )
                    begin
                         set  @sum  =  @sum + @new_ID + @comma
                        print @sum
                    end
            end

        end
     if (@rowCount  >=   0 )
        begin
            update SEC_USRDATA Set USERID  =  @sum Where current of curr1
            FETCH NEXT FROM curr1 INTO @userid
        end
     else
        begin
            Raiserror( ' Update Date failure ' , 10 , 1 )
             break
        end
    
END
-- CLOSE the CURSOR --
CLOSE curr1
-- Deallocate the CURSOR --
DEALLOCATE curr1