Delete records From Multiple Table using where IN clause

Question

I want to delete data from 2 tables based on plan id which comes from the plan_temp table using WHERE IN .. I would be very grateful if you guys help me

this is my code now I want to make this a single query

DELETE FROM plan WHERE id_plan IN(SELECT id_plan FROM plan_temp WHERE no=1184);


DELETE FROM plan_temp WHERE id_plan IN(SELECT id_plan FROM plan_temp WHERE no=1184);

The question has already been answered here [Solution](https://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql) — Passiv Programmer, Oct 19 '20 at 08:45

nbk · Answer 1 · 2020-10-19T09:09:51.197

0

Use an INNER JOIN to find the rows in both tables

DELETE t1,t2 FROM plan t1
        INNER JOIN
    plan_temp t2 ON t2.id_plan = t1.id_plan 
WHERE
    t1.id_plan IN (SELECT id_plan FROM (SELECT * FROM plan_temp) t3 WHERE no=1184);

Please test all queries in a test environment with backup, to see if this is really what you want

edited Oct 19 '20 at 09:09

answered Oct 19 '20 at 09:01

nbk

45,398
8
30
47

doesn't work "Table 't2' is specified twice, both as a target for 'DELETE' and as a separate source for data" – Oct 19 '20 at 09:07

score 0 · Answer 2 · answered Oct 19 '20 at 09:35

Unfortunately, you can't delete two separated tables.

DELETE FROM plan WHERE id_plan = 1184

DELETE FROM plan_temp WHERE id_plan = 1184

But, you can use Inner Join to delete both values from two tables. So, it has to be like that;

DELETE  plan, plan_temp FROM plan INNER JOIN plan_temp
Where
plan.id_plan = plan_temp.id_plan and id_plan = 1184;

Delete records From Multiple Table using where IN clause

2 Answers2