两表联合更新方式

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,120)) b where rn<120-@updnum) a

fetch next from cc into @code

end

close cc

deallocate cc

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!