update a set a.partno = (case when a.partno = b.partno then t.unode else partno - 1 end) from tb a join tb b on a.uid + 1 = b.uid
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-17 09:07:04 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([uid] int,[partno] int,[unode] int) insert [tb] select 1,3,4 union all select 2,3,5 union all select 3,4,5 union all select 4,4,6 union all select 5,3,7 --------------开始查询-------------------------- update a set a.partno = (case when a.partno = b.partno then b.unode else -1 end) from tb a , tb b where a.uid + 1 = b.uidselect * from tb ----------------结果---------------------------- /* uid partno unode ----------- ----------- ----------- 1 5 4 2 -1 5 3 6 5 4 -1 6 5 3 7(5 行受影响) */
如果不是自增列 则需要用到ROW_NUMBER 函数
居然是 -1 不是减去1update a set a.partno = (case when a.partno = b.partno then t.unode else -1 end) from tb a join tb b on a.uid + 1 = b.uid
update a
set a.partno = (case when a.partno = b.partno then t.unode else partno - 1 end)
from tb a join tb b on a.uid + 1 = b.uid
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-17 09:07:04
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([uid] int,[partno] int,[unode] int)
insert [tb]
select 1,3,4 union all
select 2,3,5 union all
select 3,4,5 union all
select 4,4,6 union all
select 5,3,7
--------------开始查询--------------------------
update
a
set
a.partno = (case when a.partno = b.partno then b.unode else -1 end)
from
tb a , tb b
where
a.uid + 1 = b.uidselect * from tb
----------------结果----------------------------
/* uid partno unode
----------- ----------- -----------
1 5 4
2 -1 5
3 6 5
4 -1 6
5 3 7(5 行受影响)
*/
居然是 -1 不是减去1update a
set a.partno = (case when a.partno = b.partno then t.unode else -1 end)
from tb a join tb b on a.uid + 1 = b.uid