使用 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 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 WHEREstatus
= '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` ....
相关文章