you could try something like this:

delete from qnaires, questions, sub_questions,
options, options_answers, respondents
using qnaires q, questions qu, sub_questions sq,
options o, options_answers oa, respondents r
where q.qnaire_id=qu.qnaire_id
and qu.q_id=sq.q_id
and sq.sub_question_id=o.sub_question_id
and o.option_id=oa.option_id
and r.qnaire_id=q.qnaire_id
and q.qnaire_id=15;

What does the 'using' keyword do?

I can't look it up on any search engine because it tends to think I mean the english word using, so I get "using sql to...".

I did got an error when I ran the query (Unknown table 'qnaires' in MULTI DELETE), aparently you need the aliases on both the select and the using.

[edited by: Ninjabear at 8:25 am (utc) on May 4, 2007]

MySql may not allow you to call delete on more then one table at a time.

Try this:

delete from qnaires
where qnaire_id in (select qu.qnaire_id
from
questions qu, sub_questions sq,
options o, options_answers oa, respondents r and qu.q_id=sq.q_id
and sq.sub_question_id=o.sub_question_id
and o.option_id=oa.option_id
and r.qnaire_id=q.qnaire_id
and q.qnaire_id=15);

You will have to have one delete statement per table you want to delete from. Check the MySql documentation about multi table delete statements. you might be able to do it.... I don't use mySql a lot so I am not sure on this point.

If you are going to do this in multiple delete statements make sure it is all done in one transaction. That way if you delete any data that you are using to draw relations that the relations will remain in tact until the entire delete statement is executed.

Otherwise you could loose relations and then you will be left with data that doesn't relate to anything but can't be looked up in a generic query.