使用 MySQL LEFT JOIN 删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2763206/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Deleting rows with MySQL LEFT JOIN
提问by fabrik
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 SELECTthe jobs/deadlines that meets my criteria with a LEFT JOIN:
我可以轻松地SELECT通过以下方式找到符合我标准的工作/截止日期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'
(statusbelongs to jobtable not deadline)
（status属于job表不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
jobON deadline.job_id = job.job_id WHEREstatus= 'szaml' at line 1
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 1 行的“LEFT JOIN
jobON Deadline.job_id = job.job_id WHEREstatus= 'szaml”附近使用的正确语法
How can I turn my SELECTinto a working DELETEquery?
我怎样才能把我的SELECT变成一个有效的DELETE查询？
回答by Daniel Vassallo
You simply need to specify on which tables to apply the DELETE.
您只需指定要在哪些表上应用DELETE.
Delete only the deadlinerows:
仅删除deadline行：
DELETE `deadline` FROM `deadline` LEFT JOIN `job` ....
Delete the deadlineand jobrows:
删除deadline和job行：
DELETE `deadline`, `job` FROM `deadline` LEFT JOIN `job` ....
Delete only the jobrows:
仅删除job行：
DELETE `job` FROM `deadline` LEFT JOIN `job` ....
回答by Roman Losev
If you are using "table as", then specify it to delete.
如果您使用“table as”，则指定要删除。
In the example i delete all table_1 rows which are do not exists in table_2.
在示例中，我删除了 table_2 中不存在的所有 table_1 行。
DELETE t1 FROM `table_1` t1 LEFT JOIN `table_2` t2 ON t1.`id` = t2.`id` WHERE t2.`id` IS NULL
回答by Francisco Soto
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 列。
回答by Zon
Try this:
尝试这个：
DELETE `deadline`
FROM `deadline`
INNER JOIN `job` ON `deadline`.`job_id` = `job`.`id`
WHERE `job`.`id` = 123
回答by Tahir
MySQL allows you to use the INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table.
MySQL 允许您在 DELETE 语句中使用 INNER JOIN 子句从表中删除行和另一个表中的匹配行。
For example, to delete rows from both T1 and T2 tables that meet a specified condition, you use the following statement:
例如，要从 T1 和 T2 表中删除满足指定条件的行，请使用以下语句：
DELETE T1, T2
FROM T1
INNER JOIN T2 ON T1.key = T2.key
WHERE condition;
Notice that you put table names T1 and T2 between the DELETE and FROM keywords. If you omit T1 table, the DELETE statement only deletes rows in T2 table. Similarly, if you omitT2 table, the DELETE statement will delete only rows in T1 table.
请注意，您将表名 T1 和 T2 放在 DELETE 和 FROM 关键字之间。如果省略 T1 表，则 DELETE 语句仅删除 T2 表中的行。同样，如果省略 T2 表，则 DELETE 语句将仅删除 T1 表中的行。
Hope this help.
希望这有帮助。
