sql server批量更新更新连续编号

程序员思维

于 2023-07-27 11:27:54 发布

阅读量646

点赞数

分类专栏： SQL Server 文章标签：数据库 java sql

本文链接：https://blog.csdn.net/qq_35434967/article/details/131956515

版权

SQL Server 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

sql server批量更新更新连续编号

DECLARE @i INT ,
    @id INT ,
    @no VARCHAR(50);
DECLARE curr CURSOR
FOR
    SELECT  Id
    FROM    dbo.Parts
    WHERE   State = 1
            AND MachineId = 2;
OPEN curr;
FETCH NEXT FROM curr INTO @id;
SET @i = 1;
WHILE ( @@FETCH_STATUS = 0 )
    BEGIN
        SET @no = CASE WHEN LEN(CONVERT(VARCHAR(3), @i)) = 1
                       THEN '00' + CONVERT(VARCHAR(3), @i)
                       WHEN LEN(CONVERT(VARCHAR(3), @i)) = 2
                       THEN '0' + CONVERT(VARCHAR(3), @i)
                       WHEN LEN(CONVERT(VARCHAR(3), @i)) = 3
                       THEN CONVERT(VARCHAR(3), @i)
                       ELSE ''
                  END;
        UPDATE  dbo.Parts
        SET     PartNo = 'P_00000' + @no
        WHERE   Id = @id;
        SET @i = @i + 1;
        FETCH NEXT FROM curr INTO @id;
    END;

CLOSE curr;
DEALLOCATE curr;