Click here to Skip to main content
15,915,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
The below is table1

id1 id2 address1 address2
1 3 e f
1 5 i j
1 6 k l


The below is table 2

id1 id2 address
1 1 NULL
1 2 NULL
1 3 NULL
1 4 NULL
1 5 NULL
1 6 NULL
1 7 NULL

Now i want to update table 2 with table 1.

The output should be as below:
id1 id2 address
1 1 e
1 2 e
1 3 f
1 4 i
1 5 j
1 6 l
1 7 k

Thankful for your suggestions.

Thanks,
Sreeram.
Posted

1 solution

Quite a bizarre scenario. 1st thing 1st, best way to share your sample data is to share it in a proper manner it will help us to recognize the data.

Something like this :

SQL
Declare @Table1 table (
					id1 int,
					id2 int,
					address1 varchar(20),
					address2  varchar(20)
					)

insert into @Table1
select 1, 3, 'e','f'	union all
select 1, 5, 'i','j'	union all
select 1, 6, 'k','l'


Declare @Table2 table (
					id1 int,
					id2 int,
					address1 varchar(20)
					)


insert into @Table2
select 1, 1,'NULL'  union all  
select 1, 2,'NULL'	union all
select 1, 3,'NULL'	union all
select 1, 4,'NULL'	union all
select 1, 5,'NULL'	union all
select 1, 6,'NULL'	union all
select 1, 7,'NULL';


you should do the same practice for desired output. Anyways let go for the solution. Query will working on SQL SERVER 2012 and above version.

Note: this solution is solely based on sample data

Following is the solution:

SQL
Select t2.id1, t2.id2, case when t1.id2 = t2.id2 then t1.address2 else t1.address1 end
from 
(
	Select M.id1, M.id2, M.address1, M.address2, case when previd is null then m.id1 else previd + 1 end as startID
			,case when M.id2 = lastId and M.id2 < rng.maxid2 then rng.maxid2 else M.id2 end as endID
	from (
		Select t1.id1, t1.id2, t1.address1, t1.address2
			, LAG(t1.id2 , 1) OVER (Partition by t1.id1 order by t1.id1 ) as previd
			, LAST_VALUE(t1.id2)OVER (Partition by t1.id1 order by t1.id1 ) as lastId
		from @Table1 t1
	) M
	join 
	( 
		Select t2.id1, max(t2.id2) AS maxid2
		from @Table2 t2
		Group by t2.id1
	) rng on m.id1 = rng.id1

) t1
join @Table2 t2 on t1.id1 = t2.id1 and t2.id2 between t1.startID and t1.endID


hope it helps.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900