update tb set ... where ... --条件控制就可以! --楼主给出你的测试数据和结果,这样看不出来什么。
update tb set col1=t.col1,col2=t.col2 from tb a,(select id=row_number()over(paitition by col1 order by getdate())b where a.col1=b.col and b.id=1
update tb set ... from tb t where val = (select min(val) from tb where id = t.id)update tb set ... from tb t where not exists (select 1 from tb where id = t.id and val < t.val)update tb set ... from tb t where val = (select top 1 val from tb where id = t.id order by val)
其中id为你第一列,val为你第二列.
update tb set ... from tb t where not exists (select 1 from tb where id = t.id and val < t.val)报什么错,这个就可以。
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-28 16:30:39 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] varchar(5),[col2] int) insert [tb] select '00002',1 union all select '00002',2 union all select '00002',3 union all select '00003',1 union all select '00003',2 union all select '00003',3 --------------开始查询-------------------------- update b set col1='00004',col2=10 from tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb where col2=1)b where a.col1=b.col1 and b.id=1 select * from tb ----------------结果---------------------------- /* col1 col2 ----- ----------- 00004 10 00002 2 00002 3 00004 10 00003 2 00003 3(6 行受影响) */
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2011-03-28 16:30:39 -- Verstion: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) -- Jul 9 2008 14:43:34 -- Copyright (c) 1988-2008 Microsoft Corporation -- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col1] varchar(5),[col2] int) insert [tb] select '00002',1 union all select '00002',2 union all select '00002',3 union all select '00003',1 union all select '00003',2 union all select '00003',3 --------------开始查询-------------------------- update b set col1='00004',col2=10 from tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb )b where a.col1=b.col1 and b.id=1 select * from tb ----------------结果---------------------------- /* col1 col2 ----- ----------- 00004 10 00002 2 00002 3 00004 10 00003 2 00003 3(6 行受影响) */
那就row_number() over(partition by col order by getdate()) = 1
update tb set val='val' where id =(select top 1 id from tb)
update tb
set ...
where ... --条件控制就可以!
--楼主给出你的测试数据和结果,这样看不出来什么。
tb
set
col1=t.col1,col2=t.col2
from
tb a,(select id=row_number()over(paitition by col1 order by getdate())b
where
a.col1=b.col
and
b.id=1
update tb
set ...
from tb t where val = (select min(val) from tb where id = t.id)update tb
set ...
from tb t where not exists (select 1 from tb where id = t.id and val < t.val)update tb
set ...
from tb t where val = (select top 1 val from tb where id = t.id order by val)
set ...
from tb t where not exists (select 1 from tb where id = t.id and val < t.val)报什么错,这个就可以。
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-28 16:30:39
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(5),[col2] int)
insert [tb]
select '00002',1 union all
select '00002',2 union all
select '00002',3 union all
select '00003',1 union all
select '00003',2 union all
select '00003',3
--------------开始查询--------------------------
update
b
set
col1='00004',col2=10
from
tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb where col2=1)b
where
a.col1=b.col1
and
b.id=1
select * from tb
----------------结果----------------------------
/* col1 col2
----- -----------
00004 10
00002 2
00002 3
00004 10
00003 2
00003 3(6 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-03-28 16:30:39
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col1] varchar(5),[col2] int)
insert [tb]
select '00002',1 union all
select '00002',2 union all
select '00002',3 union all
select '00003',1 union all
select '00003',2 union all
select '00003',3
--------------开始查询--------------------------
update
b
set
col1='00004',col2=10
from
tb a,(select id=row_number()over(partition by col1 order by getdate()),* from tb )b
where
a.col1=b.col1
and
b.id=1
select * from tb
----------------结果----------------------------
/* col1 col2
----- -----------
00004 10
00002 2
00002 3
00004 10
00003 2
00003 3(6 行受影响)
*/
那就row_number() over(partition by col order by getdate()) = 1