sql批量修改字段某一指定部分+修改重复编号
例子1：将
TD012025031201
TD012025031202
TD012025031203
批量改为
TD022025031201
TD022025031202
TD022025031203
语句如下：
UPDATE 表A
SET code = REPLACE(code, 'TD0120250312', 'TD0220250312')
WHERE code LIKE '%TD0120250312%';
例子2：将编号重复如TD01202503063557，现在需要修改成各不相同的编号
查询发现TD01202503069557未被使用，现在修改成TD01202503069557+2位序号
sql语句如下：
-- 先批量修改
update 表A set code='TD01202503069557' where code='TD01202503063557'
-- 将id和对应序号插入临时表
select id, ROW_NUMBER() OVER (ORDER BY id) AS RowNum
nto #Row
from 表A where code='TD01202503069557'
-- 查看下即将拼接的2个字段
select a.code,b.RowNum
FROM 表A a
JOIN #Row b ON a.id = b.id;
-- 拼接编号和序号
UPDATE a
SET a.code = a.code+cast(b.RowNum as nvarchar(2))
FROM 表A a
JOIN #Row b ON a.id = b.id;