SQL Tree Delete by Parent ID

Question

So I have a DB (Tree) that looks like this:

enter image description here

So when I delete a questions I want to delete the whole tree linked to that questions but I don't know how to do this :(. Do you have any ideas?

I have this query but it deletes only the question and the questions linked to that question but it stops there.

DELETE FROM support_faq WHERE id=".$id_intrebare." OR parinte=".$id_intrebare

Which DBMS are you using? Oracle? Postgres? Sounds like you need a recursive query. — , Apr 02 '14 at 08:48
possible duplicate of [How to recursively delete items from table?](http://stackoverflow.com/questions/9259448/how-to-recursively-delete-items-from-table) — Tony, Apr 02 '14 at 09:34
MySQL does not support recursive queries. You will need to write a stored procedure — , Apr 02 '14 at 09:38

score 1 · Accepted Answer · answered Apr 02 '14 at 08:10

1

Add a FOREIGN key with ON DELETE CASCADE option

for example

ALTER TABLE ChildTable ADD FOREIGN KEY (columnName_c) REFERENCES ParentTable(columnName_p) ON DELETE CASCADE;

answered Apr 02 '14 at 08:10

Hardik Vinzava

So first of all, you said that I must create 2 tables from my table? I have ID,TYPE,PARINTE(PARRENT) AND TEXT. – Alexandru Coman Apr 02 '14 at 08:38
@user3175636 you have only one table? if yes than use sub-query for achieving your result. – Hardik Vinzava Apr 02 '14 at 09:50
I can't use sub-query because I don't know what questions I have linked to my deleted question. – Alexandru Coman Apr 02 '14 at 10:01
After doing this my queries(DELETE, INSERT) doesn't work. Any idea why? – Alexandru Coman Apr 02 '14 at 11:01

score 0 · Answer 2 · answered Apr 02 '14 at 09:28

If you have only one table with loosely coupled parent and child relationship. You can write a procedure to delete your record recursively.

2 Answers2