两表联合更新方式

 

oracle下:

update housea a set a.extendinfo=(select b.extendinfo from houseb b where a.houseid=b.houseid)

where a.isimage=1 and a.extendinfo is null and exists(select 1 from houseb b where a.houseid=b.houseid)

 

sqlserver下:

update a set a.extendinfo=b.extendinfo from housea a,houseb b where a.houseid=b.houseid

 

两者各不通用.第二种更简洁

下面是一个存储过程,为更新某条件之外的一部分数据.

alter procedure TQ_leasehouse

as

declare @code varchar(20)

declare cc cursor for select managercode from mlsheadcompany where companycode=201001472

open cc

fetch next from cc into @code while (@@FETCH_STATUS=0)

begin

declare @updnum int

select @updnum = COUNT(1) from rent_house.dbo.house_lease_sh_mls where agentcode=@code and registdate>CONVERT(datetime,GETDATE(),120)

update a set registdate=GETDATE() from (select * from (select registdate,ROW_NUMBER() over(order by registdate) rn

from rent_house.dbo.house_lease_sh_mls where agentcode=@code and registdate<CONVERT(datetime,GETDATE(),120)) b where rn<120-@updnum) a

fetch next from cc into @code

end

close cc

deallocate cc