SQL删除基于另一个表的行 [英] SQL Delete Rows Based on Another Table

问题描述

这可能很容易，但这是星期一早上.我有两个桌子:

This is probably very easy, but it's Monday morning. I have two tables:

表1:

Field        | Type             | Null | Key | Default | Extra
id           | int(32) unsigned | NO   | PRI | NULL    | auto_increment
group        | int(32)          | NO   |     | 0       |                

表2:

Field     | Type             | Null | Key | Default | Extra
group     | int(32)          | NO   |     | 0       | 

忽略其他字段...我想要一条SQL DELETE语句，该语句将删除存在与Table1.group相等的Table2.group的Table1中的所有行.因此，如果Table1的一行具有group = 69，则当且仅当Table2中存在具有group = 69的一行时，才应删除该行.

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.

谢谢您的帮助.

推荐答案

我认为这是您想要的:

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

这篇关于SQL删除基于另一个表的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！