有一数据库,,我想进行指定数量的批量更新,比如把这个库里的所有值大于20的数据都减少10,,但是满足条件的数据不管有多少,,按照code的不同,每个code只更新10条,,,不知道这个怎么实现?我是新手,,希望高手能解释的详细一点。
还有一个是按照上面我说的更新的同时,还存在另外一个和它有相同code但没有关联的表,我希望上面更新的同时,能让那个表也同时更新,我知道用触发器可以实现,,但是具体怎么写命令不是很清楚,,希望学习。
还有一个是按照上面我说的更新的同时,还存在另外一个和它有相同code但没有关联的表,我希望上面更新的同时,能让那个表也同时更新,我知道用触发器可以实现,,但是具体怎么写命令不是很清楚,,希望学习。
http://topic.csdn.net/u/20090622/14/dcbf4ccf-1cf2-477f-9125-22e4c368b9fe.html?63674
declare @i int
set @i=1
while @i<20
begin
insert tab
select @i,'test'+cast(@i as varchar),'1'
set @i=@i+1
endset @i=1
while @i<20
begin
insert tab
select @i,'test'+cast(@i as varchar),'2'
set @i=@i+1
endselect messageid,title,typeid
from (select *,id=row_number() over (partition by typeid order by typeid) from tab) t
where id<=10messageid title typeid
----------- ---------- -----------
1 test1 1
2 test2 1
3 test3 1
4 test4 1
5 test5 1
6 test6 1
7 test7 1
8 test8 1
9 test9 1
10 test10 1
1 test1 2
2 test2 2
3 test3 2
4 test4 2
5 test5 2
6 test6 2
7 test7 2
8 test8 2
9 test9 2
10 test10 2(20 行受影响)
--用游标
declare @str varchar(100)
--定义游标
declare DZCursor CURSOR for SELECT distinct code FROM tb order by code
--打开游标
open DZCursor
--从游标取记录
fetch next from DZCursor into @str
--当有记录
while @@fetch_status=0
begin
set rowcount 10
update tb set col=col-10 where code=@str and col>20
--取下一条记录
fetch next from DZCursor into @str
end
--关闭游标
close DZCursor
--删除游标引用
deallocate DZCursor
2、
create trigger trig_update_tb on tb
for update as
update a set col=a.col-10 from othertable a,inserted b where a.code=b.code and a.col>20
http://blog.csdn.net/sdhdy/archive/2009/06/07/4249668.aspx
for update as
update a set col=a.col-10 from othertable a,inserted b where a.code=b.code and a.col>20
这种触发器最简单
from (select *,id=row_number() over (partition by sex order by sex) from employee) t
where id<=10
---------------------------------------------------------------------
A
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
0027 20:23:58 2008-09-24 7
5027 20:16:28 2008-09-27 8
----------------------------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27
--------------------------------------------------------------------
CODE TIME DATE DATA
5000 19:53:40 2008-09-10 6
0027 20:23:58 2008-09-24 7
5027 20:16:28 2008-09-27 8
----------------------------------------------------
B
CODE DATA TIME DATE
5000 20 14:37:34 2008-09-01
5000 10 08:02:17 2008-07-10
0027 20 12:05:59 2008-09-24
0027 20 10:03:21 2008-08-01
0027 10 08:00:10 2008-08-10
5027 20 11:07:00 2008-09-27