MySQL 如何根据另一个表中的值删除一个表中的记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8291830/
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
How to delete records in one table based on the values in another table?
提问by user784637
Here are two tables:
这里有两个表：
table1
表格1
cm_id cost
1 6.52
2 16.52
3 2.12
4 7.14
5 19.09
6 11.52
7 0.12
table2
表2
um_id order_num name
1 517 tommy
2 518 bobby
3 519 scotty
4 520 faris
5 521 justine
6 522 sadie
7 523 nicole
cm_id and um_id represent the same thing so the cost can be tied to each order number, ie
cm_id 和 um_id 代表相同的东西，所以成本可以绑定到每个订单号，即
SELECT table1.cm_id, table1.cost, table2.order_num, table2.order_num
FROM table1, table2
WHERE table1.cm_id=table2.um_id;
What is the single SQL statement I can use to delete rows from table1 where the order_num in table2 is between 518 and 520?
我可以用来从 table1 中删除 table2 中 order_num 在 518 和 520 之间的行的单个 SQL 语句是什么？
回答by Zohaib
delete
from table1
where cm_id IN (select um_id from table2 where order_num between 518 and 520)
回答by Elias Hossain
DELETE table1
FROM table1 INNER JOIN table2 ON table1.cm_id = table2.um_id
AND (table2.order_num BETWEEN 518 AND 520)
--OR
DELETE
FROM table1
USING table1 INNER JOIN table2 ON table1.cm_id = table2.um_id
WHERE (table2.order_num BETWEEN 518 AND 520)
EDIT:
编辑：
There was a duplicate FROMand query has been changed as per Andriy Mcomments.
有一个重复FROM，查询已根据Andriy M评论更改。
回答by bk00041
I prefer this way
我更喜欢这种方式
delete from table1
using table1, table2
where table1.cm_id = table2.um_id
and table2.order_num >= 518
and table2.order_num <= 520;
回答by Arnon Rotem-Gal-Oz
use DELETE with subquery:
将 DELETE 与子查询一起使用：
DELETE FROM table1 WHERE table1.cm_id IN (SELECT table2.um_id FROM table2 WHERE order_num>=518 and order_num<=520)
回答by jafarbtech
As INhas some performance limitations we can use delete command with simple join query like
由于IN有一些性能限制，我们可以将删除命令与简单的连接查询一起使用，例如
delete x from table1 x,table2 y where x.cm_id=y.um_id;
