How Can I Delete Rows Using a LEFT JOIN in MySQL?

Deleting Rows Using LEFT JOIN in MySQL

In MySQL, creating a LEFT JOIN can be a powerful tool for retrieving data from multiple tables. However, attempting to use that same LEFT JOIN syntax for a DELETE operation can result in ambiguity and errors.

The Problem

Consider a scenario where we have a "deadline" table and a "job" table. Each job has a status, and certain statuses indicate that the associated deadline should be deleted. A LEFT JOIN can be used to select the desired rows:

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'

However, when attempting to delete these rows using the same LEFT JOIN syntax:

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 will throw an error.

The Solution

To successfully delete the rows, we need to explicitly specify the tables to be affected. This is done by placing the table names before the DELETE keyword.

Deleting Only Deadline Rows

To delete only the deadline rows, use the following query:

DELETE `deadline` 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'

Deleting Deadline and Job Rows

To delete both the deadline and job rows, use the following query:

DELETE `deadline`, `job` 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'

Deleting Only Job Rows

To delete only the job rows, use the following query:

DELETE `job` 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'

The above is the detailed content of How Can I Delete Rows Using a LEFT JOIN in MySQL?. For more information, please follow other related articles on the PHP Chinese website!