Hi,
Below is sample data to play with,
[code] Declare @ParentMemberGroup table
(
IdParentMemberGroup int primary key identity(1,1),
IdParentMember int,
IdNewParent int,
JoinDate datetime
) ;
Declare @ChildMemberGroup table
( IdChildMemberGroup int primary key identity(1,1),
IdParentMember int,
IdChildMember int,
JoinDate datetime
);
insert into @ParentMemberGroup(IdParentMember,IdNewParent,JoinDate)
select 100,150,GETDATE()-20 union all
select 101,151,GETDATE()-19 union all
select 102,152,GETDATE()-20
insert into @ChildMemberGroup(IdParentMember,IdChildMember,JoinDate)
select 100,1000,GETDATE()-20 union all
select 100,1001,GETDATE()-19 union all
select 100,1005,GETDATE()-20 union all
select 101,1006,GETDATE()-20 union all
select 101,1007,GETDATE()-19 union all
select 101,1006,GETDATE()-20 union all
select 102,1008,GETDATE()-20 union all
select 102,1009,GETDATE()-19 union all
select 102,1002,GETDATE()-20;
Required output
;With RequiredOutput as
(
select 150,1000,GETDATE()-20 union all
select 150,1001,GETDATE()-19 union all
select 150,1005,GETDATE()-20 union all
select 150,100,GETDATE()-20 union all
select 151,1006,GETDATE()-20 union all
select 151,1007,GETDATE()-19 union all
select 151,1006,GETDATE()-20 union all
select 151,101,GETDATE()-20 union all
select 152,1008,GETDATE()-20 union all
select 152,1009,GETDATE()-19 union all
select 152,1002,GETDATE()-20 union all
select 152,100,GETDATE()-20
)
select * from RequiredOutput; [/code]
The requirement is i need to check if IdParentMember has any IdNewParent then IdNewParent will become as parent and IdParentMember will become as child for IdNewParent. so this has to be added as child record in @ChildMemberGroup and need to update the existing parent with new parent.
Below is my try
[code] if exists(select P.* from @ParentMemberGroup P join @ChildMemberGroup C on(P.IdParentMember = C.IdParentMember and IdNewParent is not null))
BEGIN
insert into @ChildMemberGroup(IdParentMember,IdChildMember,JoinDate)
select P.IdNewParent,P.IdParentMember,p.JoinDate from @ParentMemberGroup P where IdNewParent is not null
update C set C.IdParentMember = P.IdNewParent, C.JoinDate = P.JoinDate from @ParentMemberGroup P join @ChildMemberGroup C on(P.IdParentMember = C.IDParentMember)
where P.IdNewParent is not null;
END
select * from @ChildMemberGroup; [/code]
Is there any better way to achieve thi insert/update logic. Please help me