Deleting rows with MySQL LEFT JOIN

Question

You simply need to specify on which tables to apply the DELETE.

Delete only the deadline rows:

DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....

Delete the deadline and job rows:

DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....

Delete only the job rows:

DELETE `job` FROM `deadline` LEFT JOIN `job` ....

If you are using "table as", then specify it to delete.

In the example i delete all table_1 rows which are do not exists in table_2.

DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL

DELETE FROM deadline where ID IN (
    SELECT d.ID FROM `deadline` d LEFT JOIN `job` ON deadline.job_id = job.job_id WHERE `status` =  'szamlazva' OR `status` = 'szamlazhato' OR `status` = 'fizetve' OR `status` = 'szallitva' OR `status` = 'storno');

I am not sure if that kind of sub query works in MySQL, but try it. I am assuming you have an ID column in your deadline table.

Try this:

DELETE `deadline`
FROM `deadline`
INNER JOIN `job` ON `deadline`.`job_id` = `job`.`id`
WHERE `job`.`id` = 123

This script worked for me:

DELETE t
FROM table t
INNER JOIN join_table jt ON t.fk_column = jt.id
WHERE jt.comdition_column…;

MySQL allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table.

For example, to delete rows from both T1 and T2 tables that meet a specified condition, you use the following statement:

DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;

Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.

Hope this help.