It's not strictly that I need to change the column names... I was planning to
use them within the same query like this:
[code]
<cfquery name="breadcrumb" datasource="#REQUEST.theDatabase#">
<!---SELECT the values for the Deleted level into variables--->
DECLARE DeletedAreaName VARCHAR(50);
DECLARE DeletedLft INTEGER;
DECLARE DeletedRgt INTEGER;
SELECT SiteAreaName, Lft, Rgt
INTO DeletedAreaName, DeletedLft, DeletedRgt
FROM directoryLinks
WHERE SiteAreaName = '#ARGUMENTS.SiteAreaName#';
<!---Perform the deletion--->
DELETE FROM directoryLinks
WHERE Lft BETWEEN DeletedLft AND DeletedRgt;
<!---UPDATE the table so that the gaps between Lft
and Rgt values are removed--->
UPDATE directoryLinks
SET Lft = CASE WHEN Lft > DeletedLft THEN
Lft - (DeletedRgt - DeletedLft + 1)
ELSE
Lft
END,
Rgt = CASE WHEN Rgt > DeletedLft THEN
Rgt - (DeletedRgt - DeletedLft + 1)
ELSE
Rgt
END
WHERE Lft > DeletedLft
OR Rgt > DeletedLft;
</cfquery>
[/code]
NB. Forgot to previously mention thatI'm using MX 7 & SQL server 2000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:244618
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
