SQLTeam.com | Weblogs | Forums

Help Needed in Relational logic


#1

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


#2

Which IdParentMember? there's one in each table. Also, how does the column IdParentMember "have any" IdNewParent? This is not clear to me


#3

Hi gb,

Thanks for your reply

its kind of parent child relationship and basically i am trying to updated oldparent with new parent if exists for the children and old parent will also become as child of new parent. Please execute the my sample try against the test data i gave it.

please ht the below screen print to see required output


#4

I can't see the benefit of the "IF" statement. Why not just:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET XACT_ABORT ON

BEGIN TRANSACTION

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
INNER JOIN @ChildMemberGroup C
      ON (P.IdParentMember = C.IDParentMember)
WHERE P.IdNewParent IS NOT NULL;

COMMIT

#5

hi gb,

thanks for the query and the reason for if would be, before we do insert/update i need to check if the any row has newparent. if yes then need to apply the insert/update logic.

Do yo have any sugestion?


#6

I get that, but the same check is executed in the WHERE clauses. so there's no benefit that i can see


#7

Agreed. Thanks for your time. Hope this can done using sql merge as well.please advice me which one is best "sql merge vs above logic?


#8

MERGE will not run any better, though it is less code to write.


#9

Does merge make any performance issue?


#10

If you're really lucky, it may run faster due to one pass over the data. If unlucky maybe not. If really unlucky, the results may not be what you want. Merge has a number of open issues


#11

Alright, thanks for your time on this thread. Appreciated your help. Have a great weekend.