Geek Answers Handbook

Delete all related records from multiple tables

I have a table for polling and you want to delete all records related to one specific page.

My tables:

_______________ _______________ ___________ ________________ |_tblSurvey___| |_tblAnswers___| |_tblAlt__| |_tblQuestions_| | surveyID | | answerAltID | | altID | | questID | | surveyName | | userID | | altText | | questText | |_____________| |______________| |_questID_| |_surveyID_____|

Say I want to delete all entries related to surveyID 1.

I tried:

 DELETE * FROM tblSurvey, tblQuestions, tblAlt, tblAnswers WHERE tblSurvey.surveyID = 1 AND tblsurvey.surveyID = tblQuestions.surveyID AND tblQuestions.questID = tblAlt.questID AND tblAlt.altID = tblAnswers.answerAltID
+7
source share
2 answers

Two ways:

  • Set foreign key constraints using ON DELETE CASCADE .
  • Use a statement with multiple DELETE tables.

Try the following:

 DELETE tblSurvey, tblQuestion, tblAlt, tblAnswers FROM tblSurvey JOIN tblQuestion ON tblsurvey.surveyID = tblQuestion.surveyID JOIN tblAlt ON tblQuestions.questID = tblAlt.questID JOIN tblAnswers ON tblAlt.altID = tblAnswers.answerAltID WHERE tblSurvey.surveyID = 1
+7
source

If your tables have foreign key references, you can use ON DELETE CASCADE

+2
source

More articles:


All Articles