SQL删除基于另一个表的行

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.

感谢您的任何帮助。

5 个解决方案

#1

I think this is what you want:

我想这就是你想要的：

DELETE FROM `table1`
WHERE `group` in (SELECT DISTINCT `group` FROM `table2`)

#2

I think this way is faster:

我觉得这种方式更快：

DELETE FROM t1 USING table1 t1 INNER JOIN table2 t2 ON ( t1.group = t2.group );

#3

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

此致，Arno Brinkman

#4

Something like this

像这样的东西

delete from table1 where group in (select group from table2)

#5

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上有大量行，这可能会更好。有人可以请我直截了当吗？

#1