Hi All,
Tried both combinations of both way but couldn't get it to work.
In the end I had to break it into multiple queries.
[code]
<!--- GET Delete --->
<cfquery name="breadcrumb1" datasource="#REQUEST.theDatabase#">
<!---SELECT the values for the Deleted level into variables--->
SELECT SiteAreaName, Lft, Rgt
FROM directoryLinks
WHERE SiteAreaName = '#ARGUMENTS.SiteAreaName#'
</cfquery>
<cfset DeletedAreaName = '#breadcrumb1.SiteAreaName#'>
<cfset DeletedLft = '#breadcrumb1.Lft#'>
<cfset DeletedRgt = '#breadcrumb1.Rgt#'>
<!--- Delete --->
<cfquery name="breadcrumb2" datasource="#REQUEST.theDatabase#">
<!---Perform the deletion--->
DELETE FROM directoryLinks
WHERE Lft BETWEEN <cfqueryparam value="#DeletedLft#" cfsqltype="cf_sql_integer"
/> AND <cfqueryparam value="#DeletedRgt#" cfsqltype="cf_sql_integer" />;
</cfquery>
<!---UPDATE the table so that the gaps between Lft
and Rgt values are removed--->
<!--- GET Delete --->
<cfquery name="breadcrumb3" datasource="#REQUEST.theDatabase#">
UPDATE directoryLinks
SET Lft = (CASE WHEN Lft > <cfqueryparam value="#DeletedLft#"
cfsqltype="cf_sql_integer" /> THEN
Lft - (<cfqueryparam value="#DeletedRgt#"
cfsqltype="cf_sql_integer" /> - <cfqueryparam value="#DeletedLft#"
cfsqltype="cf_sql_integer" /> + 1)
ELSE
Lft
END),
Rgt = (CASE WHEN Rgt > <cfqueryparam value="#DeletedLft#"
cfsqltype="cf_sql_integer" /> THEN
Rgt - (<cfqueryparam value="#DeletedRgt#"
cfsqltype="cf_sql_integer" /> - <cfqueryparam value="#DeletedLft#"
cfsqltype="cf_sql_integer" /> + 1)
ELSE
Rgt
END)
WHERE Lft > <cfqueryparam value="#DeletedLft#" cfsqltype="cf_sql_integer" />
OR Rgt > <cfqueryparam value="#DeletedLft#" cfsqltype="cf_sql_integer" />
</cfquery>
[/code]
Thanks for all you help.
Richard
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244632
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
