--问题:数据库有50W条数据,要求按顺序更新某列,更新的值为按顺序生成的值1,2,3,4.....
--如表#TMP数据,按ID 的顺序更新COLUMN2的值,COLUMN2值对应的是1,2,3,4,5......
--更新的时候表不会锁死
--ID COLUMN1 COLUMN2
--1 AAAA 1
--2 BBBB 2
--2 CCCC 3
--7 DDDD 4
--9 FFFF 5
--......
--......
CREATE TABLE #TMP
(
ID INT,
COLUMN1 VARCHAR(10),
COLUMN2 VARCHAR(10)
)
INSERT INTO #TMP SELECT 1,'AAAA',''
INSERT INTO #TMP SELECT 2,'BBBB',''
INSERT INTO #TMP SELECT 2,'CCCC',''
INSERT INTO #TMP SELECT 7,'DDDD',''
INSERT INTO #TMP SELECT 9,'FFFF',''
......
......
......
SELECT * FROM #TMP
alter table tb add newid int
go
;with cte as
(
select id + 0 as id,column1,column2,newid = identity(int,1,1)
from tb
)udpate a
set a.newid = b.newid
from tb a join cte b on a.id = b.id
set B.COLUMN2=A.temp
from #temp1 A,#TMP B
where A.id=B.id and A.COLUMN1=b.COLUMN1
SELECT ID,INDEXID=ROW_NUMBER() OVER(ORDER BY ID) INTO #TMP01 FROM #TMP
UPDATE #TMP SET #TMP.COLUMN2=#TMP01.INDEXID FROM #TMP,#TMP01 WHERE #TMP.ID=#TMP01.ID
(
ID INT,
COLUMN1 VARCHAR(10),
COLUMN2 VARCHAR(10)
)
INSERT INTO #TMP SELECT 1,'AAAA',''
INSERT INTO #TMP SELECT 2,'BBBB',''
INSERT INTO #TMP SELECT 2,'CCCC',''
INSERT INTO #TMP SELECT 7,'DDDD',''
INSERT INTO #TMP SELECT 9,'FFFF',''declare @no int
set @no=0
update #TMP set COLUMN2=RTRIM(@no),@no=@no+1
SELECT * FROM #TMP/*
ID COLUMN1 COLUMN2
----------- ---------- ----------
1 AAAA 1
2 BBBB 2
2 CCCC 3
7 DDDD 4
9 FFFF 5
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-04-18 11:11:19
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([pkID] int,[Name] varchar(3),[SortID] sql_variant)
insert [tb]
select 1,'AAA',null union all
select 2,'BBB',null union all
select 3,'CCC',null union all
select 4,'DDD',null union all
select 5,'EEE',null union all
select 6,'FFF',null union all
select 7,'GGG',null union all
select 8,'HHH',null union all
select 9,'III',null union all
select 10,'JJJ',null
--------------开始查询--------------------------
update
tb
set
SortID=b.id-1
from
tb a,
(select id=ROW_NUMBER()over(order by GETDATE()),* from tb)b
where
a.Name=b.Name
and
a.pkID=b.pkID
select * from tb
----------------结果----------------------------
/* pkID Name SortID
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 AAA 0
2 BBB 1
3 CCC 2
4 DDD 3
5 EEE 4
6 FFF 5
7 GGG 6
8 HHH 7
9 III 8
10 JJJ 9(10 行受影响)
*/
--谢谢楼上各位,怎么处理类似下面的数据,
--第9,10条的sortID如果为空,怎么根据pkID的顺序更新ShortID为对应的 8,9?
----------------结果----------------------------
/* pkID Name SortID
1 AAA 0
2 BBB 1
3 CCC 2
4 DDD 3
5 EEE 4
6 FFF 5
7 GGG 6
8 HHH 7
9 III
10 JJJ --我自己的处理办法如下,有参照4楼:
declare @no int
set @no=(select max(column2) from #tmp where column2 is not null)
update #TMP set COLUMN2=RTRIM(@no),@no=@no+1 where isnull(column2,'')=''
所以还得更新column2为空的数据,将column2按顺序更新,
对应6楼,
假如pkID为9,10 是新增的,那么对应的SortID 则更新为SortID的最大值+1,所以最后pkID NAME SortID
9 III 8
10 JJJ 9
create table t1
(
id int,
col1 varchar(10),
col2 int
)
INSERT INTO t1 SELECT 1,'AAAA',''
INSERT INTO t1 SELECT 2,'BBBB',''
INSERT INTO t1 SELECT 2,'CCCC',''
INSERT INTO t1 SELECT 7,'DDDD',''
INSERT INTO t1 SELECT 9,'FFFF',''
select * from t1update t1
set col2=a.row
from (select ROW_NUMBER() over(order by id) as row,* from t1) a
inner join t1 on a.id=t1.id以上为SQL2005及以上版本,SQL2000不会了。