有这样一个表
I S E
5 2008/02/02 2008/05/02
5 2008/03/02 2008/05/02
5 2008/04/02 null现想要更新这个表
在I值相同的条件下
用E值段来更新S值段更新后的结果应该是这样的
I S E
5 2008/02/02 2008/03/02
5 2008/03/02 2008/04/02
5 2008/04/02 nullS值段值最大的不被更新请问应该怎样实现?
谢谢
I S E
5 2008/02/02 2008/05/02
5 2008/03/02 2008/05/02
5 2008/04/02 null现想要更新这个表
在I值相同的条件下
用E值段来更新S值段更新后的结果应该是这样的
I S E
5 2008/02/02 2008/03/02
5 2008/03/02 2008/04/02
5 2008/04/02 nullS值段值最大的不被更新请问应该怎样实现?
谢谢
set e=(select min(s) from tb a where a.i=tb.i and a.s>tb.s)
go
insert into tb select 5, '2008/02/02', '2008/05/02'
insert into tb select 5, '2008/03/02', '2008/05/02'
insert into tb select 5, '2008/04/02', null
go
select i,convert(char(10),s,120) as s,convert(char(10),e,120) as e from tb
update tb
set e=(select min(s) from tb a where a.i=tb.i and a.s>tb.s)
select i,convert(char(10),s,120) as s,convert(char(10),e,120) as e from tb
go
drop table tb
goi s e
----------- ---------- ----------
5 2008-02-02 2008-05-02
5 2008-03-02 2008-05-02
5 2008-04-02 NULL(3 行受影响)(3 行受影响)i s e
----------- ---------- ----------
5 2008-02-02 2008-03-02
5 2008-03-02 2008-04-02
5 2008-04-02 NULL(3 行受影响)