-- обновление поля address_address
--insert into #kladr_fulladdress select address_id from dbo.address (nolock) where address_upddt > getdate()-1 and pmuser_updid=1
/* #count #nocount
with countaa (aa) as 
(
	select count(1) aa from dbo.address a (nolock)
	inner join dbo.klstreet s (nolock) on a.klstreet_id = s.klstreet_id and s.klstreet_upddt > getdate()-1 and s.pmuser_updid=1
	union
	select count(1) aa from dbo.address a(nolock) 
	inner join dbo.klarea s (nolock) on a.klcity_id = s.klarea_id and s.klarea_upddt > getdate()-1 and s.pmuser_updid=1
	union
	select count(1) aa from dbo.address a(nolock) 
	inner join dbo.klarea s (nolock) on a.kltown_id = s.klarea_id and s.klarea_upddt > getdate()-1 and s.pmuser_updid=1
	union
	select count(1) aa from dbo.address a(nolock) 
	inner join dbo.klarea s (nolock) on a.klsubrgn_id = s.klarea_id and s.klarea_upddt > getdate()-1 and s.pmuser_updid=1
	union
	select count(1) aa from dbo.address a(nolock) 
	inner join dbo.klarea s (nolock) on a.klrgn_id = s.klarea_id and s.klarea_upddt > getdate()-1 and s.pmuser_updid=1
	)
select sum(aa) from countaa
*/
do $$
Begin
	create table tmp.gpp_kladr_fulladdress as
	select * from 
	(
		select address_id from dbo.address a 
		inner join dbo.klstreet s on a.klstreet_id = s.klstreet_id and s.klstreet_upddt > now()::date-1 and s.pmuser_updid=1
		union
		select address_id from dbo.address a
		inner join dbo.klarea s on a.klcity_id = s.klarea_id and s.klarea_upddt > now()::date-1 and s.pmuser_updid=1
		union
		select address_id from dbo.address a
		inner join dbo.klarea s on a.kltown_id = s.klarea_id and s.klarea_upddt > now()::date-1 and s.pmuser_updid=1
		union
		select address_id from dbo.address a
		inner join dbo.klarea s on a.klsubrgn_id = s.klarea_id and s.klarea_upddt > now()::date-1 and s.pmuser_updid=1
		union
		select address_id from dbo.address a
		inner join dbo.klarea s on a.klrgn_id = s.klarea_id and s.klarea_upddt > now()::date-1 and s.pmuser_updid=1
	) as address_id;

	while exists(select 1 from tmp.gpp_kladr_fulladdress limit 1)
	LOOP
		create table tmp.gpp_kladr_address as select * from tmp.gpp_kladr_fulladdress limit 5000;
		update dbo.address 
		set address_address = dbo.address_address(address_id), address_upddt = getdate(), pmuser_updid=1 
		where address_id in (select address_id from tmp.gpp_kladr_address);
		delete from tmp.gpp_kladr_fulladdress where address_id in (select address_id from tmp.gpp_kladr_address);
		drop table tmp.gpp_kladr_address;
	END LOOP;

	drop table tmp.gpp_kladr_fulladdress;
end $$
