使用 MySQL LEFT JOIN 删除行

2021-11-20 00:00:00 left-join mysql sql-delete delete-row

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

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

相关文章