CODEWITHSUNDEEP
sqlmysql
nedblorf
nedblorf

Reputation: 5365

SQL Delete Rows Based on Another Table

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

Table1:

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

Table2:

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.

Thank you for any help.

Upvotes: 36

Views: 82655

Answers (6)

jafarbtech
jafarbtech

Reputation: 7025

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

Upvotes: 1

Jay
Jay

Reputation: 5043

I think this is what you want:

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

Upvotes: 49

BT26
BT26

Reputation: 340

I think this way is faster:

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

Upvotes: 23

ArnoBrinkman
ArnoBrinkman

Reputation: 89

The nice solution is just writing the SQL as you say it yourself already:

DELETE FROM Table1
WHERE
  EXISTS(SELECT 1 FROM Table2 WHERE Table2.Group = Table1.Group)

Regards, Arno Brinkman

Upvotes: 8

expedient
expedient

Reputation: 618

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?

Upvotes: 1

u07ch
u07ch

Reputation: 13702

Something like this

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

Upvotes: 7

Related Questions