MySQL SQL删除基于另一个表的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1783784/
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
SQL Delete Rows Based on Another Table
提问by nedblorf
This is probably very easy, but it's Monday morning. I have two tables:
这可能很容易，但现在是星期一早上。我有两个表：
Table1:
表格1：
Field | Type | Null | Key | Default | Extra
id | int(32) unsigned | NO | PRI | NULL | auto_increment
group | int(32) | NO | | 0 |
Table2:
表2：
Field | Type | Null | Key | Default | Extra
group | int(32) | NO | | 0 |
Ignoring other fields...I would like a single SQL DELETE statement that will delete all rows in Table1 for which there exists a Table2.group equal to Table1.group. Thus, if a row of Table1 has group=69, that row should be deleted if and only if there exists a row in Table2 with group=69.
忽略其他字段...我想要一个 SQL DELETE 语句，该语句将删除 Table1 中存在等于 Table1.group 的 Table2.group 的所有行。因此，如果 Table1 的一行具有 group=69，则当且仅当 Table2 中存在 group=69 的行时，才应删除该行。
Thank you for any help.
感谢您的任何帮助。
回答by Jay
I think this is what you want:
我认为这就是你想要的：
DELETE FROM `table1`
WHERE `group` in (SELECT DISTINCT `group` FROM `table2`)
回答by BT26
I think this way is faster:
我认为这种方式更快：
DELETE FROM t1 USING table1 t1 INNER JOIN table2 t2 ON ( t1.group = t2.group );
回答by ArnoBrinkman
The nice solution is just writing the SQL as you say it yourself already:
好的解决方案就是按照您自己已经说过的方式编写 SQL：
DELETE FROM Table1
WHERE
EXISTS(SELECT 1 FROM Table2 WHERE Table2.Group = Table1.Group)
Regards, Arno Brinkman
问候， 阿诺·布林克曼
回答by u07ch
Something like this
像这样的东西
delete from table1 where group in (select group from table2)
回答by expedient
Off the top of my head:
在我的头顶：
delete from Table1 where id in (select id from table1 inner join table2 on Table1.group = Table2.group)
I did this a little differently than other posters -- I think if there is a large number of rows on Table2 this might be better. Can someone please set me straight on that?
我这样做与其他海报略有不同——我认为如果 Table2 上有大量行，这可能会更好。有人可以让我直截了当吗？
回答by jafarbtech
you can delete either table rows by using its alias in a simple join query like
您可以通过在简单的连接查询中使用其别名来删除任一表行，例如
delete a from table1 a,table2 b where a.uid=b.id and b.id=57;
here, you might specify either a or b to delete the corresponding table rows
在这里，您可以指定 a 或 b 来删除相应的表行
