使用MySQL LEFT JOIN删除行 [英] Deleting rows with MySQL LEFT JOIN

问题描述

我有两个表，一个表用于工作截止日期，一个表用于描述工作.每个作业可以具有一个状态，某些状态表示必须从另一个表中删除作业的截止日期.

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.

我可以通过LEFT JOIN轻松地SELECT符合我的条件的工作/期限:

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属于job表而不是deadline)

但是当我想从deadline中删除这些行时，MySQL会引发错误.我的查询是:

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错误什么也没说:

MySQL error says nothing:

您的SQL语法有错误；检查与您的MySQL服务器版本相对应的手册以获取正确的语法，以在'LEFT JOIN job ON截止日期附近使用.job_id= job.job_id WHERE status ='szaml'在第1行

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

如何将SELECT转换为有效的DELETE查询?

How can I turn my SELECT into a working DELETE query?

推荐答案

您只需要指定在哪些表上应用DELETE.

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

仅删除deadline行:

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

删除deadline和job行:

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

仅删除job行:

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

这篇关于使用MySQL LEFT JOIN删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！