Deleting rows with MySQL LEFT JOIN

Question

I have two tables  one for job deadlines  one for describe a job  Each job can take a status and some statuses means the jobs  deadlines must be deleted from the other table   I can easily SELECT the jobs deadlines that meets my criteria with a LEFT JOIN   SELECT   FROM  deadline  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     status belongs to job table not deadline   But when I d like to delete these rows from deadline  MySQL throws an error  My query is   DELETE FROM  deadline  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    MySQL error says nothing      You have an error in your SQL syntax  check the manual that corresponds to your MySQL server version for the right syntax to use near  LEFT JOIN job ON deadline job id   job job id WHERE status    szaml  at line 1   How can I turn my SELECT into a working DELETE query

User · Accepted Answer

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` ....

User · Answer

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

User · Answer

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

User · Answer

Try this   DELETE  deadline  FROM  deadline  INNER JOIN  job  ON  deadline   job id     job   id  WHERE  job   id    123

User · Answer

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

User · Answer

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] Deleting rows with MySQL LEFT JOIN

Examples related to mysql

Examples related to left-join

Examples related to delete-row

Examples related to sql-delete