本文介绍了使用其他表的列从表中删除的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我创建了一个过程,在该过程中我创建了一个临时表来存储列的值,然后我想使用这些列值来删除其他表中的数据.我如何在 while 循环中执行此操作,或者我可以使用 join 从 AppCoverLetter、AppError 中删除行,哪种方式更好?
I have created a procedure in which I create a temporary table to store the value of a column and then I want to use those column values to delete the data in other tables. How do I do this in while loop or simply can I use join to delete rows from AppCoverLetter, AppError which way is better?
CREATE DEFINER=`root`@`localhost` PROCEDURE `gdpr_delete`(_email_ varchar(128)) BEGIN DECLARE NumberRecords int; DECLARE RowCount int ; -- Create a temporary table, note the IDENTITY -- column that will be used to loop through -- the rows of this table CREATE TABLE GdprDeleteData (AppId INT NOT null); -- Insert the resultset we want to loop through -- into the temporary table INSERT INTO GdprDeleteData (AppId) SELECT AppId FROM ApplyData.AppFormData where lower(Email) = lower(_email_); -- Get the number of records in the temporary table SET NumberRecords = ROW_COUNT(); SET RowCount = 1; select NumberRecords, RowCount; -- loop through all records in the temporary table -- using the WHILE loop construct start transaction; DELETE AppCoverLetter FROM AppCoverLetter inner JOIN GdprDeleteData ON AppCoverLetter.AppID = GdprDeleteData.AppId; DELETE AppError FROM AppError inner JOIN GdprDeleteData ON AppError.AppID = GdprDeleteData.AppId; DELETE AppFormData FROM AppFormData inner JOIN GdprDeleteData ON AppFormData.AppID = GdprDeleteData.AppId; DELETE AppJobData FROM AppJobData inner JOIN GdprDeleteData ON AppJobData.AppID = GdprDeleteData.AppId; DELETE AppTrackingData FROM AppTrackingData inner JOIN GdprDeleteData ON AppTrackingData.AppID = GdprDeleteData.AppId; DELETE FlowLog FROM FlowLog inner JOIN GdprDeleteData ON FlowLog.AppID = GdprDeleteData.AppId; DELETE App FROM App inner JOIN GdprDeleteData ON App.AppID = GdprDeleteData.AppId; DELETE AppCoverLetter FROM AppCoverLetter inner JOIN GdprDeleteData ON AppCoverLetter.AppID = GdprDeleteData.AppId; DELETE AppResume FROM AppResume inner JOIN GdprDeleteData ON AppResume.AppID = GdprDeleteData.AppId; commit; -- drop the temporary table drop table GdprDeleteData; END 推荐答案你可以避免临时表和循环在mysql中,您可以在单个查询中删除两个表并使用sunquery作为临时数据
You could avoid the temporary table and loop in mysql you can delete form the two table in single query and use a sunquery for the temporary data
DELETE t1, t2 FROM AppCoverLetter t1 INNER JOIN ( SELECT AppId FROM ApplyData.AppFormData where lower(Email) = lower(_email_) ) t3 ON t1.AppID = t3.AppId INNER JOIN AppError t2 ON t2.AppID = t3.AppId;或者如果你有更多的桌子
or if you ahve more table
DELETE t1, t2, tx FROM AppCoverLetter t1 INNER JOIN ( SELECT AppId FROM ApplyData.AppFormData where lower(Email) = lower(_email_) ) t3 ON t1.AppID = t3.AppId INNER JOIN AppError t2 ON t2.AppID = t3.AppId INNER JOIN table3 tx ON tx.AppID = t3.AppId;更多推荐
使用其他表的列从表中删除
发布评论