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.
我有两个表格,一个是工作截止日期,一个是描述工作。每个作业都有一个状态,一些状态意味着必须从另一个表中删除作业的截止日期。
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
belongs to job
table not deadline
)
(状态属于工作表而非截止日期)
But when I'd like to delete these rows from deadline
, MySQL throws an error. My query is:
但是当我想从deadline删除这些行时,MySQL会抛出一个错误。我查询的方法是:
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 error says nothing:
MySQL错误说什么:
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您的SQL语法有错误;请检查与MySQL服务器版本对应的手册,以便在“截止日期”附近使用“左侧连接作业”。job_id =工作。job_id,其中第1行状态= 'szaml'
How can I turn my SELECT
into a working DELETE
query?
如何将SELECT转换为可工作的删除查询?
3 个解决方案
#1
268
You simply need to specify on which tables to apply the DELETE
.
您只需指定要应用DELETE的表。
Delete only the deadline
rows:
只删除截止日期行:
DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....
Delete the deadline
and job
rows:
删除截止日期和工作行:
DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....
Delete only the job
rows:
只删除作业行:
DELETE `job` FROM `deadline` LEFT JOIN `job` ....
#2
27
If you are using "table as", then specify it to delete.
如果使用的是“表as”,则指定要删除它。
In the example i delete all table_1 rows which are do not exists in table_2.
在本例中,我删除了表_2中不存在的所有表_1行。
DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL
#3
1
DELETE FROM deadline where ID IN (
SELECT d.ID FROM `deadline` d 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');
I am not sure if that kind of sub query works in MySQL, but try it. I am assuming you have an ID column in your deadline table.
我不确定这种子查询在MySQL中是否有效,但请尝试一下。我假设你在截止日期表中有一个ID列。
#1
268
You simply need to specify on which tables to apply the DELETE
.
您只需指定要应用DELETE的表。
Delete only the deadline
rows:
只删除截止日期行:
DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....
Delete the deadline
and job
rows:
删除截止日期和工作行:
DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....
Delete only the job
rows:
只删除作业行:
DELETE `job` FROM `deadline` LEFT JOIN `job` ....
#2
27
If you are using "table as", then specify it to delete.
如果使用的是“表as”,则指定要删除它。
In the example i delete all table_1 rows which are do not exists in table_2.
在本例中,我删除了表_2中不存在的所有表_1行。
DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL
#3
1
DELETE FROM deadline where ID IN (
SELECT d.ID FROM `deadline` d 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');
I am not sure if that kind of sub query works in MySQL, but try it. I am assuming you have an ID column in your deadline table.
我不确定这种子查询在MySQL中是否有效,但请尝试一下。我假设你在截止日期表中有一个ID列。