SQLSERVER 2008批量更新

[一只写bug的程序猿]

需要更新SQLSERVER 表
UPDATE TABLE SET FLAG = '1' WHERE CODE = @VALUE

一次更新100条数据，需要怎么做，

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([FLAG] NVARCHAR(100))
Insert #T
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) AS num from #T
)
UPDATE cte SET FLAG='1' WHERE num<3		--100的话这里换成100

SELECT * FROM #T

[吉普赛的歌]

SET ROWCOUNT 100
UPDATE  TABLE SET  FLAG = '1'  WHERE  CODE  = @VALUE AND FLAG='0'

[一只写bug的程序猿]

引用 1 楼 sinat_28984567 的回复:

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([FLAG] NVARCHAR(100))
Insert #T
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) AS num from #T
)
UPDATE cte SET FLAG='1' WHERE num<3		--100的话这里换成100

SELECT * FROM #T

如果不使用存储过程呢？

[二月十六]

引用 3 楼 a364416036 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([FLAG] NVARCHAR(100))
Insert #T
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2 union all
select 2
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) AS num from #T
)
UPDATE cte SET FLAG='1' WHERE num<3		--100的话这里换成100

SELECT * FROM #T

如果不使用存储过程呢？[/quote] 额，没用存储过程啊。。。用的就是普通语句，不用管测试数据，直接看语句

[bryanwang1234]

UPDATE TABLE SET FLAG = '1' WHERE CODE in (select top 100 CODE FROM TABLE where FLAG = '0')