Delete data from table without creating temporary table

Question

I have three tables, that match the following diagram:

And I need to delete some data from join_table, where a label column(table_right) and name column(table_left) match some criteria.

My solution is to use a temporary table:

create temporary table if not exists data_for_deletion
(select jt.id
 from join_table jt
          left join table_left tableLeft on jt.table_left_id = tableLeft.id
          left join table_right tableRight on jt.table_right_id = tableRight.id
 where tableLeft.name = 'name' and tableRight.label = 'label');

delete from join_table where id in (select id from data_for_deletion);

My question is: is there any other way to do such deletion without creating a temporary table?

Yes, use your select as a subquery in your last query.. . https://stackoverflow.com/questions/17548751/how-to-write-a-sql-delete-statement-with-a-select-statement-in-the-where-clause/43461074 or with a delete with inner http://www.mysqltutorial.org/mysql-delete-join/ — Mate, Dec 24 '19 at 07:51
No, I can not. When I put select statement in the last query, I have following error: You can't specify target table 'join_table' for update in FROM clause. — Bohdan Gaponec, Dec 24 '19 at 07:54
check the examples... or with a delete with inner join http://www.mysqltutorial.org/mysql-delete-join/ — Mate, Dec 24 '19 at 07:55

score 1 · Accepted Answer · answered Dec 24 '19 at 08:07

You should be able to use MySQL's multi-table DELETE syntax:

DELETE jt
FROM join_table jt
JOIN table_left tl ON jt.table_left_id = tl.id
JOIN table_right tr ON jt.table_right_id = tr.id
WHERE tl.name = 'name' AND tr.label = 'label'

Note that since you have a WHERE clause which is dependent on columns in table_left and table_right there is no point in using a LEFT JOIN as it will be converted to an INNER JOIN anyway (see the manual).

Delete data from table without creating temporary table

1 Answers1