Menu
I have a table for a survey, and wants to delete all records that are related to one certain survery.
My tables:
_______________ _______________ ___________ ________________
|_tblSurvey___| |_tblAnswers___| |_tblAlt__| |_tblQuestions_|
| surveyID | | answerAltID | | altID | | questID |
| surveyName | | userID | | altText | | questText |
|_____________| |______________| |_questID_| |_surveyID_____|
Let's say I wanna delete all records that are 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
322
The syntax also supports deleting rows from multiple tables at once. To delete rows from both tables where there are matching id values, name them both after the DELETE keyword: DELETE t1, t2 FROM t1 INNER JOIN t2 ON t1.id = t2.id; What if you want to delete nonmatching rows?
We know how to delete data from a single table, however, if you are confronted with a situation where you want to delete data from one table and also any related data from other tables, you can employ the multi-table delete in SQL.
MySQL DELETE JOIN with INNER JOIN MySQL also allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table. Notice that you put table names T1 and T2 between the DELETE and FROM keywords.
Two ways:
ON DELETE CASCADE.DELETE statement.Try this:
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
