Cascading Record Update

maybe something like this, but with all the tags and closing tags:

idlist = "1"; // or whatever you want to start with
records = 1;

<cfloop condition = "records gt 0">
<cfquery name = "q1">
select categoryid
from tablename
where categoryParentid in (#idlist#)
and categoryid not in (#idlist#)

records = q1.recordcount;
idlist = ListAppend(idlist, ValueList(q1.categoryid);
</cfloop>

<cfquery>
update yourtable
set categoryactive = 0
where categoryid in (#idlist#)

OK, let me try again. Assuming the category is a character field then wouldn't this work?

UPDATE tableName
SET categoryActive = 0
WHERE categoryID LIKE 'x%' OR categoryParentID = 'x%'

Where x is the highest level category you want to remove. For example if x is 'A' then all 'A's would be removed but if x is 'A-2-2' then all at and below 'A-2-2' would be removed; correct?

smnbin wrote:
> Hello,
>
> I have a table in my ecommerce application with the following fields:
>
> categoryID
> categoryParentID
> categoryTitle
> categoryActive
>
> The categoryParentID is the categoryID of the corresponding parent category in
> the hierarchy of categories and there could be a potentially unlimited number
> of sub category tiers.
>
> For example:
>
> Category A
> Category A-1
> Category A-2
> Category A-2-1
> Category A-2-2
> Category A-2-2-1
> etc
> Category B
> Category B-1
> Category B-2
> Category C
> Category C-1
> Category C-2
>
> It is possible to disable a category by changing the value in the
> categoryActive column to zero. However, I would like to be able to cascade this
> update to all corresponding sub categories. For example, if I disable Category
> A, the status of all corresponding sub categories would be changed to zero too.
>
> Does anyone know the best way to achieve this? I'm sure this must be a common
> problem, but I'm totally stuck!

I don't think it's possible only with SQL. In ColdFusion you can make a
function along these lines:

function disableCateg( categID ) {
SQL: update table set categoryActive = false where id = #categID#
for(child in get_all_children) {
disableCateg( child )
}
}

--
Mack

I'm afraid to answer this because it seems like an easy answer to me. Wouldn't this work?

UPDATE tableName
SET categoryActive = 0
WHERE categoryID = 'A' OR categoryParentID = 'A'

Never mind, wrong answer.