Update Parent/Child Relationship Query 1

[SgtJarrow]

This is not my area of expertise, but is probably pretty easy...If someone can help out it would be greatly appreciated.

Given the below query:

select
	pst.cnfrm_prod_grp_sub_prod_trans_type_assn_id
	, pst.cnfrm_prod_grp_code
	, st.cnfrm_sub_prod_type_code
	, st.cnfrm_trans_type_code
	, pst.parent_assn_id
from dbo.cnfrm_prod_grp_sub_prod_trans_type_assn pst
left join dbo.cnfrm_sub_prod_trans_type_assn st
	on pst.cnfrm_sub_prod_trans_type_assn_id = st.cnfrm_sub_prod_trans_type_assn_id
where cnfrm_prod_grp_code = 'agy'
order by pst.cnfrm_prod_grp_code
	, st.cnfrm_sub_prod_type_code
	, st.cnfrm_trans_type_code

I get the following (abbreviated) results:

cnfrm_prod_grp_sub_prod_trans_type_assn_id	cnfrm_prod_grp_code	cnfrm_sub_prod_type_code	cnfrm_trans_type_code	parent_assn_id
1374	agy	NULL	NULL	1372
1391	agy	adi	NULL	NULL
11	agy	adi	buy	NULL
16	agy	adi	can	NULL
14	agy	adi	oob	NULL
15	agy	adi	oos	NULL
17	agy	adi	rev	NULL
12	agy	adi	sel	NULL
13	agy	adi	whi	NULL
1392	agy	bfc	NULL	NULL
18	agy	bfc	buy	NULL
19	agy	bfc	can	NULL
20	agy	bfc	oob	NULL
21	agy	bfc	oos	NULL
22	agy	bfc	rev	NULL
23	agy	bfc	sel	NULL
1393	agy	dbt	NULL	NULL

I need to create an update query to update the last column (parent_id) for each record. Of course, parent is a record that has the last column NULL, so in the sample data, id 11 should have a parent of 1391, id 1391 should have a parent id of 1394, etc.

Anyone have a starting point they can help me out with?? Thanks.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[simian336]

How did you determine 11 should have a parent of 1391, id 1391 should have a parent id of 1394, etc.

Simi

 

[RyanEK]

Hi,

Try adding a rowid to the source table...

with cteData (rowid, cnfrm_prod_grp_sub_prod_trans_type_assn_id, cnfrm_prod_grp_code, cnfrm_sub_prod_type_code, cnfrm_trans_type_code, parent_assn_id) as (
select 
  ROW_NUMBER() over(order by (select 0)) as rowid, 
   pst.cnfrm_prod_grp_sub_prod_trans_type_assn_id,
   pst.cnfrm_prod_grp_code,
   st.cnfrm_sub_prod_type_code,
   t.cnfrm_trans_type_code,
   pst.parent_assn_id
from dbo.cnfrm_prod_grp_sub_prod_trans_type_assn pst
left join dbo.cnfrm_sub_prod_trans_type_assn st on pst.cnfrm_sub_prod_trans_type_assn_id = st.cnfrm_sub_prod_trans_type_assn_id
where cnfrm_prod_grp_code = 'agy'
order by pst.cnfrm_prod_grp_code, st.cnfrm_sub_prod_type_code, st.cnfrm_trans_type_code)

update u
set u.parent_assn_id = 
 (select top 1 cnfrm_prod_grp_sub_prod_trans_type_assn_id
  from cteData t 
  where t.rowid < d.rowid order by t.rowid desc)
from cnfrm_prod_grp_sub_prod_trans_type_assn pst as u
join cteData d on d.cnfrm_prod_grp_sub_prod_trans_type_assn_id = u.cnfrm_prod_grp_sub_prod_trans_type_assn_id
and d.parent_assn_id  is null

I suggest you run in a transaction first.

Ryan

 

[SgtJarrow]

That for replying...I had to leave work early yesterday due to a medical issue, but am back and ready to go over this with some help.

Ryan, I am not having much luck with your query suggestions. I am currently trying to tweak it to work and verify, but it is not liking something in the cte setup.

simi, good question and I thought it was kind of visible in the sample data...Let me elaborate. Given the following four lines:

1372    ***    NULL    NULL    NULL
1374    agy    NULL    NULL    1372
1391    agy    adi     NULL    NULL
11      agy    adi     buy     NULL

The logic for parenting in as follows: 1372 is a special case..it is the most senior parent of every other record. 1374 is a first level record. I know it is a first level parent because the agy in column 1 is the only value present. The other two columns are NULL. Record 1391 is a second level record and should have a parent of 1374. How do I know this?? Because there is an agy in column 1, some value in column 2, and a NULL in column 3. Record 11 is a third level record. This is the lowest possible. Its parent should be 1391. Why again? Because the first two columns match what is in 1391 and the last column has some value.

I guess a brief description of why I am doing this might help as well...I inherited this database and the previous person created a series of three tables and two association tables to create this parent child relationship. I have to use a combination of 5 INNER and LEFT JOINS in order to determine inheritance/parents. I am wanting to replace that with just a parent/child self join in a single table. This will make the queries and sprocs I have been asked to maintain and add a lot easier to write...

Thanks for any and all suggestions.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[simian336]

To simply things I recreated your structure with some simpler names... See if this works.

Simi

/*
create table test(
id varchar(5),
f1 varchar(5),
f2 varchar(5),
f3 varchar(5),
pid varchar(5))

*/

delete from test

INSERT INTO test (id,f1,f2,f3,pid) values ('1374','agy','','','1372')
INSERT INTO test (id,f1,f2,f3,pid) values ('1391','agy','adi','','')
INSERT INTO test (id,f1,f2,f3,pid) values ('11','agy','adi','buy','')
INSERT INTO test (id,f1,f2,f3,pid) values ('16','agy','adi','can','')
INSERT INTO test (id,f1,f2,f3,pid) values ('14','agy','adi','oob','')
INSERT INTO test (id,f1,f2,f3,pid) values ('15','agy','adi','oos','')
INSERT INTO test (id,f1,f2,f3,pid) values ('17','agy','adi','rev','')
INSERT INTO test (id,f1,f2,f3,pid) values ('12','agy','adi','sel','')
INSERT INTO test (id,f1,f2,f3,pid) values ('13','agy','adi','whi','')
INSERT INTO test (id,f1,f2,f3,pid) values ('1392','agy','bfc','','')
INSERT INTO test (id,f1,f2,f3,pid) values ('18','agy','bfc','buy','')
INSERT INTO test (id,f1,f2,f3,pid) values ('19','agy','bfc','can','')
INSERT INTO test (id,f1,f2,f3,pid) values ('20','agy','bfc','oob','')
INSERT INTO test (id,f1,f2,f3,pid) values ('21','agy','bfc','oos','')
INSERT INTO test (id,f1,f2,f3,pid) values ('22','agy','bfc','rev','')
INSERT INTO test (id,f1,f2,f3,pid) values ('23','agy','bfc','sel','')
INSERT INTO test (id,f1,f2,f3,pid) values ('1393','agy','dbt','','')

update test
set f2=null
where f2=''

update test
set f3=null
where f3=''

update test
set pid=null
where pid=''

--******************************************
with mytest2 as
(
select id, f1, f2
from test
where f3 is null
and f2 is not null
)
update a
set a.pid=b.id
from test a
join mytest2 b
on a.f1=b.f1 and a.f2=b.f2
where f3 is not null

--******************************************

with mytest1 as
(
select id, f1
from test
where f2 is null
and f1 is not null
)
update a
set a.pid=b.id
from test a
join mytest1 b
on a.f1=b.f1
where f3 is null
and f2 is not null

select *
from test

 

[SgtJarrow]

That looks spot on simi. Thanks and have a star. I will modify this to work against my tables. That should shorten my update times greatly.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer