Deleting from a table with a hierarchyid

Question

Deleting from a table with a hierarchyid

keith.west

SSC Enthusiast

Points: 170
More actions
January 13, 2009 at 2:02 pm

#205096

I am developing scripts and procs to manage a table with a hierarchyid data type. When deleting records from this table (deleting hierarchy nodes), my understanding is that the children will be orphaned if I don't update the hierarchyid columns for all of the descendants. Is there a standard way of doing this. I can't find any documentation on this. It looks like most of the examples leave the orphaned children.
Any help or input is greatly appreciated.
Keith

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

baabhu SSCertifiable Points: 6205 More actions · Answer 1

You can use a deleted flag in the table, and mark it as deleted. Rather than physical deletion of the records rows can be soft deleted.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

@baabhu - You do realize this thread is almost 3 years old?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

In that case, I wonder if the OP ever got an answer from somewhere?

Also... I don't believe you can just "soft delete" when the HierarchyID is being used.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)