两表联合更新方式

概述  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  

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

总结

以上是内存溢出为你收集整理的两表联合更新方式全部内容，希望文章能够帮你解决两表联合更新方式所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。