从多个表中删除所有相关记录 [英] Delete all related records from multiple tables

问题描述

我有一张调查表，想删除与某个调查相关的所有记录.

I have a table for a survey, and wants to delete all records that are related to one certain survery.

我的桌子:

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

假设我要删除与SurveyID 1相关的所有记录.

Let's say I wanna delete all records that are related to surveyID 1.

我尝试过:

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

推荐答案

两种方式:

• 使用ON DELETE CASCADE设置外键约束.
• 使用多表DELETE语句.

• Set up foreign key constraints with ON DELETE CASCADE.
• Use a multiple-table DELETE statement.

尝试一下:

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          

这篇关于从多个表中删除所有相关记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！