使用连接删除多个表而不是删除所有表 [英] Deleting multiple tables using join not deleting for all tables

问题描述

我正在使用连接从多个表中删除，但此查询的问题是如果一个表不包含匹配值/为空，则不会对具有匹配值和数据的表执行删除.我该如何解决?

I am deleting from multiple tables using joins, but the problem with this query is if one table doesn't contain matching values/ is empty the delete is not performed for the tables which have matching values and data. How can I resolve this?

CREATE DEFINER=`root`@`localhost` PROCEDURE `gdpr_delete`(_email_ varchar(128))
BEGIN
   DELETE AppCoverLetter, AppError, AppFormData, AppJobData, AppTrackingData, FlowLog, App,AppResume
   FROM AppCoverLetter t1
   INNER JOIN (
     SELECT AppId 
     FROM ApplyData.AppFormData 
     where lower(Email) = lower(_email_)
   ) t3 ON t1.AppID = t3.AppId
   INNER JOIN AppError        ON AppError.AppID = t3.AppId
   INNER JOIN AppCoverLetter  ON AppCoverLetter.AppID = t3.AppId
   INNER JOIN AppFormData     ON AppFormData.AppID = t3.AppId
   INNER JOIN AppJobData      ON AppJobData.AppID = t3.AppId
   INNER JOIN AppTrackingData ON AppTrackingData.AppID = t3.AppId
   INNER JOIN FlowLog         ON FlowLog.AppID = t3.AppId
   INNER JOIN App             ON App.AppID = t3.AppId
   INNER JOIN AppResume       ON AppResume.AppID = t3.AppId;
END

推荐答案

如果你不总是匹配行，你可以使用单独的删除和单个连接来避免循环

if you heve not alway matching rows you can use separated delete with single join for avoid loop

   DELETE  tx
   FROM table3 tx
   INNER JOIN (
     SELECT AppId 
     FROM ApplyData.AppFormData 
     where lower(Email) = lower(_email_)
   ) t3 ON tx.AppID = t3.AppId

但是您可能有一些具有可靠持久关系的表，然后对于这些表，您可以使用单个查询进行多次删除并将单个连接删除仅留给可选关系

But could be you have some tables with a solid persistent relation and then for these table you can use a single query for multiple delete and leave the single join delete only to the optional relation

这篇关于使用连接删除多个表而不是删除所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！