Alex Rivera | Logout

Deleting rows with MySQL LEFT JOIN

Asked 2010-05-04T06:20:32.377
238

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?

Edit
Report

1 Answer

52

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
answered 2013-05-16T12:05:01.427

Your Answer