SQL: Deleting child records recursively

[darrel]

I have a table of records that share a parent/child relationship. Ie:

id | parentID | item
1 | 0 | main item
2 | 1 | secondary item
3 | 2 | tertiary item

If I delete item 1, I then want to automatically delete items 2 and 3, since
they are children of 1.

I was going to do a recursive function that deletes item 1, then goes into
the db, looking for any item that had '1' as a parent id, delete them, and
then repeat down the chain. Not a big deal, but thought I'd ask if there was
already perhaps a built in SQL command that would do this in one query.

-Darrel

[Julian Roberts]

I tend to use the logic from this article

[url]http://www.sqlteam.com/item.asp?ItemID=8866[/url]

Then have SQL like

delete from t where lineage like '%/100/%'

--
Jules
[url]http://www.charon.co.uk/charoncart[/url]
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004