求更新语句

[wnsr]

人员表ryb,有字段身份证号sfzh,姓名xm,1时间段sj1,2时间段sj2,表里有可能一个人有多条数据,也有可能一个人只有一条数据,现在要一条更新语句,要更新每个人的最早sj1为sj2,如
xm sj1 sj2
张三 2015-4-1 2015-3-1
张三 2015-5-1 2015-3-2
更新为
xm sj1 sj2
张三 2015-3-1 2015-3-1
张三 2015-5-1 2015-3-2

[RINK_1]

UPDATE ryb SET sj1=sj2 FROM ryb A WHERE NOT EXISTS (SELECT 1 FROM ryb WHERE sfzh=A.sfzh AND xm=A.xm AND sj1<A.sj1)

[itliyi]

update ryb set bz=case when sj1>sj2 then 1 when sj1=sj2 then 2 when sj1<sj2 then 3 end

[itliyi]

说错，以为是另外那贴

if OBJECT_ID('ryb') is not null
drop table ryb
go
create table ryb(sfzh char(30),xm char(30), sj1 datetime , sj2 datetime)
go
insert into ryb(sfzh,xm,sj1,sj2) values
('11111',N'张三','2015-4-1','2015-3-1'),
('11111',N'张三','2015-5-1','2015-3-2'),
('22222',N'李四','2015-4-1','2015-3-1'),
('22222',N'李四','2015-5-1','2015-3-2')
go
select * from ryb 
go
update ryb
set sj1=sj2
from 
(
	select sfzh,MIN(sj1)sj1
	from ryb
	group by sfzh
	
)t0 
where ryb.sfzh=t0.sfzh and t0.sj1=ryb.sj1

[等不到来世]

update t
set sj1=sj2
from ryb t
where not exists(select 1 from ryb where sfzh=t.sfzh and xm=t.xm and sj1<t.sj1)