how do I update an unknown amount of child ID's?

[Scooby_Doobie_Doo]

I have a record, which can have a childID which I can update fine. My problem becomes when the childID has a Child Id, which can have multiple child ID's off that ID.

How Can I update them from the Top ID down? Meaning

Tag one gets updated, but he has two child tags. Then I update those two, but each of those have three ChildID's, then I update those three, and then only one of those has a child ID, which I need to update last due to it being dependent on the data updates of the previous ID's. I would need to do this indefinately until I run out of ID's, but I"m not sure how to accomplish such a task. I assume a loop built on the ID's, but how can I build them into an order that allows the update to run right? Any ideas?

[Newsgroup_User]

you have totally lost me at your second paragraph...

can you try and explain a little more? and maybe with a bit more
appropriate terminology?
for instance, what is it you call TAG? and why and how your childIDs are
so related and so complicated?

posting a sample table structure with sample data will probably be
helpful...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

[Dan_Bracuk]

IdList = "yourstartingid";
cfloop condition listlen(Idlist) gt 0

update yourtable
set this = that
where id in (#IdList#)

cfquery name = children
select id
from yourtable
where parentid in (#IdList#)

IdList = valueList(query.id)

end loop

[Scooby_Doobie_Doo]

Hi Dan, thank you for that example, but it doesn't help since I don't want to update the children with the same data. Each child has a calculated amount and the update amount is different per child, so I can' do an
update yourtable
set this = that
where id in (#IdList#)


I would need to do more of a
update mytable
set this = that
where id = #id#

and if there were three children, I'd have to do it three times, and if five children, five times, etc.

This would work with a loop just fine, but if there is a split in the parent ID, say a Parent ID had two children, and each of those had three children, how do you process one half of that chain and do the three child updates, and any additional updates under those child tags, and then get back up to the initial split? Doe that make sense?

Meaning, look at my attached code.

10665 should update 11134 and 11135

11135 should update 11529 and 11530 :

which is fine, but once 11530 is updated, how do I get back up and follow the 11134 ID down it's chain?
That's where I'm having an issue understanding how to do this, is it even possible with Cold Fusion?

[Scooby_Doobie_Doo · Accepted solution]

Okay, I think I got it. Here's what I did. Once I do the initial update, I search on all possible parent/child IDs and build a list of all the parent IDs.

I then loop through that list and update per each parent ID all the kids under that ID. This allows me to update the childrenID's individually in the loop.

Thanks for your help, you definately put me in the right direction and I appreciate it.