update test set name=(select top 1 name from test where id=a.id-20) from test a where id>20
2005及以上版本可用 CREATE TABLE #TP ( ID INT IDENTITY, NAME VARCHAR(10) )INSERT INTO #TP SELECT 'EVAN' INSERT INTO #TP SELECT 'JAVK' INSERT INTO #TP SELECT 'NICK' INSERT INTO #TP SELECT 'A' INSERT INTO #TP SELECT 'A' INSERT INTO #TP SELECT 'A';WITH CTE AS ( SELECT * ,ROW_NUMBER()OVER(ORDER BY ID)ROW FROM #TP t ) UPDATE t SET T.NAME=C.NAME FROM CTE t ,CTE C WHERE C.ROW=T.ROW-3-----需要替换的地方SELECT * FROM #TP t
ID NAME ----------- ---------- 1 EVAN 2 JAVK 3 NICK 4 EVAN 5 JAVK 6 NICK(6 row(s) affected)
-->******************************************************* -->Microsoft SQL Server Management Studio Complete 2008*** -->AUTHOR : Mr wang ********** -->CREATE TIME : 2010-11-18 17:37:18 ************** -->******************************************************* --> 测试数据:[TB] if object_id('[TB]') is not null drop table [TB]---->建表 create table [TB]([id] int,[name] varchar(6)) insert [TB] select 1,'cccccc' union all select 2,'dddddd' union all select 5,'a' union all select 6,'a'--> 查询结果 SELECT * FROM [TB] UPDATE n set n.name= t.name from [TB] n,(select * from TB where name <>'a') t where n.name ='a'--> 删除表格 DROP TABLE [TB]
CREATE TABLE #TP
(
ID INT IDENTITY,
NAME VARCHAR(10)
)INSERT INTO #TP SELECT 'EVAN'
INSERT INTO #TP SELECT 'JAVK'
INSERT INTO #TP SELECT 'NICK'
INSERT INTO #TP SELECT 'A'
INSERT INTO #TP SELECT 'A'
INSERT INTO #TP SELECT 'A';WITH CTE
AS
(
SELECT * ,ROW_NUMBER()OVER(ORDER BY ID)ROW
FROM #TP t
)
UPDATE t
SET T.NAME=C.NAME
FROM CTE t ,CTE C
WHERE C.ROW=T.ROW-3-----需要替换的地方SELECT * FROM #TP t
ID NAME
----------- ----------
1 EVAN
2 JAVK
3 NICK
4 EVAN
5 JAVK
6 NICK(6 row(s) affected)
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-18 17:37:18 **************
-->*******************************************************
--> 测试数据:[TB]
if object_id('[TB]') is not null
drop table [TB]---->建表
create table [TB]([id] int,[name] varchar(6))
insert [TB]
select 1,'cccccc' union all
select 2,'dddddd' union all
select 5,'a' union all
select 6,'a'--> 查询结果
SELECT * FROM [TB]
UPDATE n
set n.name= t.name
from [TB] n,(select * from TB where name <>'a') t
where n.name ='a'--> 删除表格
DROP TABLE [TB]