-- sql 2000 update tb set ... from tb m, (select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n where m.id = n.id and n.px = 2 -- sql 2005 update tb set ... from tb m, (select * , px = row_number() over(order by id) from tb t) n where m.id = n.id and n.px = 2
我晕,那没办法,加个自增列来吧SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TBUPDATE #T SET COL=... WHERE IDD=你想要的行SET ROWCOUNT 行不通了,
declare @a int set @a=2 update tb set col=@a where id=2?
你要更新是EXCEL那你直接导入数据库后,数据库中一个表加个自增列呀,
SQL2005;with t as ( select rn=row_number()over(order by getdate()),* from tb ) update t set col=@col where rn=2
declare @i int set @i=2 --2005 ;with t as ( select id=row_number(order by 排序字段),* from tb ) update t set..... from t where id=@i
sql2005使用CTE可以灵活解决UPDATE问题方法上面也说了 下面再举个小李子 --2005CTE 有顺序地加序号 WITH T1RN AS ( SELECT col1, ROW_NUMBER() OVER(ORDER BY col2) AS RowNum FROM dbo.T1 ) UPDATE T1RN SET col1 = RowNum; ------------- select * from T1 order by col1 /* col1 col2 ----------- ----- 1 A 2 A 3 A 4 B 5 B 6 C 7 C 8 C 9 C 10 C */
-------------------获取第二行数据记录-----------------------------select top 1 * from tb where [primary_key] not in (select top 1 [primary_key] from tb)
update tb
set ...
from tb m,
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n
where m.id = n.id and n.px = 2
-- sql 2005
update tb
set ...
from tb m,
(select * , px = row_number() over(order by id) from tb t) n
where m.id = n.id and n.px = 2
set @a=2
update tb set col=@a where id=2?
(
select rn=row_number()over(order by getdate()),*
from tb
)
update t set col=@col where rn=2
set @i=2
--2005
;with t as
(
select id=row_number(order by 排序字段),* from tb
)
update t set..... from t where id=@i
下面再举个小李子
--2005CTE 有顺序地加序号
WITH T1RN AS
(
SELECT col1, ROW_NUMBER() OVER(ORDER BY col2) AS RowNum
FROM dbo.T1
)
UPDATE T1RN SET col1 = RowNum;
-------------
select * from T1 order by col1
/*
col1 col2
----------- -----
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 C
9 C
10 C
*/