How Can I Delete Rows from a MySQL Table Using a LEFT JOIN?

Deleting Rows from MySQL Table Using a LEFT JOIN

Question:

You have two MySQL tables, one representing job deadlines and another describing jobs. Each job has a status, and certain statuses indicate that the corresponding deadline rows should be deleted. Using a LEFT JOIN, you can select the deadline and job rows that meet your criteria. However, when attempting to delete these rows, you encounter an error. How can you modify your query to achieve the desired deletion?

Answer:

To delete rows from a MySQL table using a LEFT JOIN, you must specify which table(s) to delete from. The error you encountered is likely due to the omission of this specification.

Solutions:

1. Delete Only Deadline Rows:

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

DELETE `deadline` FROM `deadline` LEFT JOIN `job` ON deadline.job_id = job.job_id WHERE ...;

2. Delete Both 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 ...;

3. Delete 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 ...;

In each case, replace the ... with the appropriate WHERE clause to specify the job statuses for which you want to delete the rows.

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