SQL 进阶示例

1、批量修改编号字段从CZ0001到CZ0100

 

WITH SRC AS

(

        SELECT Id,

                 idx=ROW_NUMBER()OVER(ORDER BY CreationTime)

                FROM dbo.Dict_DictItem

 where DictCategoryID='8DBA14A0-4A60-45D8-ABE6-3BFE0C9E4F17'

)

UPDATE dbo.Dict_DictItem

        SET Code ='CZ'+ right('00000000' + convert(varchar,SRC.idx),8)

FROM SRC

JOIN dbo.Dict_DictItem AS item ON item.Id = SRC.Id