表结构
ID NUM IID
1 NULL 2
2 NULL 1
3 NULL 1
4 NULL 1
5 NULL 1
6 NULL 1
现在有张表要批量更新记录编号,结构如上
怎么把IID为1的记录 批量更新为A201000001 A201000002.... 这样的记录编号更新结果ID NUM IID
1 NULL 2
2 A201000001 1
3 A201000002 1
4 A201000003 1
5 A201000004 1
6 A201000005 1
ID NUM IID
1 NULL 2
2 NULL 1
3 NULL 1
4 NULL 1
5 NULL 1
6 NULL 1
现在有张表要批量更新记录编号,结构如上
怎么把IID为1的记录 批量更新为A201000001 A201000002.... 这样的记录编号更新结果ID NUM IID
1 NULL 2
2 A201000001 1
3 A201000002 1
4 A201000003 1
5 A201000004 1
6 A201000005 1
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[NUM] [int],[IID] [int])
INSERT INTO [tb]
SELECT '1',NULL,'2' UNION ALL
SELECT '2',NULL,'1' UNION ALL
SELECT '3',NULL,'1' UNION ALL
SELECT '4',NULL,'1' UNION ALL
SELECT '5',NULL,'1' UNION ALL
SELECT '6',NULL,'1'
-->SQL查询如下:
UPDATE tb SET NUM=(SELECT COUNT(1) FROM tb T WHERE iid=TB.IID AND ID<=TB.ID)
WHERE IID=1SELECT * FROM tb
/*
ID NUM IID
----------- ----------- -----------
1 NULL 2
2 1 1
3 2 1
4 3 1
5 4 1
6 5 1(6 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[NUM] [int],[IID] [int])
INSERT INTO [tb]
SELECT '1',NULL,'2' UNION ALL
SELECT '2',NULL,'1' UNION ALL
SELECT '3',NULL,'1' UNION ALL
SELECT '4',NULL,'1' UNION ALL
SELECT '5',NULL,'1' UNION ALL
SELECT '6',NULL,'1'
-->SQL查询如下:
DECLARE @I INT
SET @I=0
UPDATE tb SET @I=@I+1,NUM=@I
WHERE IID=1SELECT * FROM tb
/*
ID NUM IID
----------- ----------- -----------
1 NULL 2
2 1 1
3 2 1
4 3 1
5 4 1
6 5 1(6 行受影响)
*/这也是一种方法
WHERE IID=1
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[NUM] [varchar](20),[IID] [int])
INSERT INTO [tb]
SELECT '1',NULL,'2' UNION ALL
SELECT '2',NULL,'1' UNION ALL
SELECT '3',NULL,'1' UNION ALL
SELECT '4',NULL,'1' UNION ALL
SELECT '5',NULL,'1' UNION ALL
SELECT '6',NULL,'1'
-->SQL查询如下:
DECLARE @I INT
SET @I=0
UPDATE tb SET @I=@I+1,NUM='A2010'+right(100000+@I,5)
WHERE IID=1SELECT * FROM tb
/*
ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 A201000001 1
3 A201000002 1
4 A201000003 1
5 A201000004 1
6 A201000005 1(6 行受影响)*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[NUM] [varchar](20),[IID] [int])
INSERT INTO [tb]
SELECT '1',NULL,'2' UNION ALL
SELECT '2',NULL,'1' UNION ALL
SELECT '3',NULL,'1' UNION ALL
SELECT '4',NULL,'1' UNION ALL
SELECT '5',NULL,'1' UNION ALL
SELECT '6',NULL,'1'
-->SQL查询如下:
UPDATE tb SET NUM=(SELECT 'A2010'+RIGHT(100000+COUNT(1),5) FROM tb T WHERE iid=TB.IID AND ID<=TB.ID)
WHERE IID=1SELECT * FROM tb
/*
ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 A201000001 1
3 A201000002 1
4 A201000003 1
5 A201000004 1
6 A201000005 1(6 行受影响)
*/
insert @t select 1,NULL,2
insert @t select 2,NULL,1
insert @t select 3,NULL,1
insert @t select 4,NULL,1
insert @t select 5,NULL,1
insert @t select 6,NULL,1select * from @t;with tb as
(
select ROW_NUMBER() over (order by id) as pid,*
from @t
where IID=1
)update tb
set NUM=right('A201000000'+CAST(pid as varchar(20)),11)select * from @t
/*
ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 NULL 1
3 NULL 1
4 NULL 1
5 NULL 1
6 NULL 1(6 行受影响)(5 行受影响)ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 A2010000001 1
3 A2010000002 1
4 A2010000003 1
5 A2010000004 1
6 A2010000005 1(6 行受影响)
*/
insert @t select 1,NULL,2
insert @t select 2,NULL,1
insert @t select 3,NULL,1
insert @t select 4,NULL,1
insert @t select 5,NULL,1
insert @t select 6,NULL,1select * from @t;with tb as
(
select ROW_NUMBER() over (order by id) as pid,*
from @t
where IID=1
)update tb
set NUM='A201'+right('000000'+CAST(pid as varchar(20)),6)select * from @t
/*
ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 NULL 1
3 NULL 1
4 NULL 1
5 NULL 1
6 NULL 1(6 行受影响)(5 行受影响)ID NUM IID
----------- -------------------- -----------
1 NULL 2
2 A201000001 1
3 A201000002 1
4 A201000003 1
5 A201000004 1
6 A201000005 1(6 行受影响)
*/
use TEMPLE
go
create table tb
(
ID int ,
NUM varchar(50) ,
IID int
)
insert into tb values('1','NULL','2')
insert into tb values('2','NULL','1')
insert into tb values('3','NULL','1')
insert into tb values('4','NULL','1')
insert into tb values('5','NULL','1')
insert into tb values('6','NULL','1')declare @AA int
declare @Numchar varchar(50)
declare @BB int
set @AA=0
declare Tb_cursor cursor for select ID,NUM from tb
open Tb_cursor
fetch next from Tb_cursor into @BB,@Numchar
while @@fetch_status=0
begin
if (@AA>=1 and @AA<=6)
begin
update tb set NUM=('A20100000'+cast(@AA as varchar(50))) where ID=@BB
end
fetch next from Tb_cursor into @BB,@Numchar
set @AA=@AA+1end
close Tb_cursor
deallocate Tb_cursor
go
select * from tb
drop table tb