求一个批量修改表的语句
本帖最后由 as198645 于 2013-08-23 11:12:06 编辑 表结构
a1a2a3
1 11
2 10
3 9
4 8
5 7
6 6 4
7 7 5
8 8 6
9 9 3
1010 2
1111 1
把a1>5的列的a2值按a3排序之后 取前五个 更新到A2 为空的那地方去
执行查询希望得到的结果
a1a2a3
11111
21010
3 9 9
4 6 8
5 7 7
6 6 6
7 7 5
8 8 4
9 9 3
1010 2
1111 1
update语句
[解决办法]
create table #tb(a1 int,a2 int,a3 int)
insert into #tb
select 1,null,11
union all select 2,null,10
union all select 3,null,9
union all select 4,null,8
union all select 5,null,7
union all select 6,6,4
union all select 7,7,5
union all select 8,8,6
union all select 9,9,3
union all select 10,10,2
union all select 11,11,1
select * from #tb
update #tb set a2=a3
where a1<=5
select * from #tb
drop table #tb
/*
a1a2a3
11111
21010
399
488
577
664
775
886
993
10102
11111
*/
[解决办法]
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
go
create table #temp( [a1] varchar(100), [a2] varchar(100), [a3] varchar(100));
insert #temp
select '1',null,'11' union all
select '2',null,'10' union all
select '3',null,'9' union all
select '4',null,'8' union all
select '5',null,'7' union all
select '6','6','4' union all
select '7','7','5' union all
select '8','8','6' union all
select '9','9','3' union all
select '10','10','2' union all
select '11','11','1'
--SQL:
;WITH cte AS
(
SELECT a.a2, newa2= b.a2 FROM
(SELECT rowid=ROW_NUMBER() OVER(ORDER BY a1),* FROM #temp WHERE a2 IS NULL) a
INNER JOIN
(select TOP(5) rowid=ROW_NUMBER() OVER(ORDER BY a3), a2 from #temp WHERE a1>5) b
ON a.rowid = b.rowid
)
update cte
SET a2 = newa2
SELECT * FROM #temp
/*
a1a2a3
11111
21010
399
468
577
664
775
886
993
10102
11111
*/