36

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.

Improve this question

6 Answers 6

Reset to default
49

I think this is what you want:

DELETE FROM `table1`
WHERE `group` in (SELECT DISTINCT `group` FROM `table2`)
Improve this answer
5
  • That's simple and very effective. Works in SQL server (but without the single quotation marks).
    – user2074102
    Commented Oct 31, 2013 at 14:45
  • p.s. remember to not do it the wrong way around ;) a real killer lol
    – AO_
    Commented Feb 17, 2014 at 16:50
  • 6
    This is indeed a solution but certainly not the only one and most definitively not the most performer one. The 'IN' operator must be always used with caution on very big tables. A better solution is the one provided by @BT26 which uses inner joins.
    – kuklei
    Commented Apr 27, 2014 at 23:50
  • 4
    As @kuklei has said, this is very resource intensive and although it works, it is essentially unusable for anything but small tables. We have a table with 1.3m rows and even limiting this to removing 500, it takes more than 5min just to prepare the query. The answer by BT26 below is the correct answer.
    – oucil
    Commented Aug 7, 2014 at 16:53
  • 1
    As the previous comments suggested, this only works for limited rows. Consider that it is turning the entirety of that into a big IN list. BT26's solution is good, esp. for the case specifically called out in the OP. However, I'd recommend @AmoBrinkman's solution in general (the EXISTS example), as it will perform better if table2 has many rows for the field joined on.
    – troy
    Commented May 17, 2016 at 17:17
23

I think this way is faster:

DELETE FROM t1 USING table1 t1 INNER JOIN table2 t2 ON ( t1.group = t2.group );
Improve this answer
3
  • In SQL Server this seems not to work: SSMS throws this at me for the above query: Incorrect syntax near 'USING'.
    – Oliver
    Commented Jan 14, 2014 at 15:29
  • 5
    In the case of SQL Server the syntax is: DELETE FROM t1 FROM t1 INNER JOIN T2 ON t1.ID = t2.ID This deletes all rows from t1 that exists on table t2 based on the id but more conditions can be added to the inner join clause as normally with the AND operator. Notice the second From?! It is there not by mistake and that is what makes the USING work on SQL Server.
    – kuklei
    Commented Apr 27, 2014 at 23:38
  • If you are deleting from really large InnoDB tables, you might consider increasing your innodb_buffer_pool_size. This first failed after 30 minutes of execution for me, so I increased to 512MB and succeeded in removing some 13 million extra records. The tables I was joining were 44 million and 23 million records, respectively to their order in the query.
    – Nikola Novak
    Commented Jun 28, 2015 at 23:17
8

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

Improve this answer
2
  • 1
    I like about this solution that it contains a WHERE clause. This means that it will still work if you want to delete only such lines which match across several columns in the two tables.
    – malamut
    Commented Feb 9, 2012 at 15:57
  • @ArnoBrinkman would adding LIMIT 1 to: EXISTS(SELECT 1 FROM Table2 WHERE Table2.Group = Table1.Group LIMIT 1) help?
    – Stefan Rogin
    Commented Jan 30, 2013 at 14:19
7

Something like this

delete from table1 where group in (select group from table2)
Improve this answer
2
  • I'd select distinct in the subquery
    – Voytek Jarnot
    Commented Nov 23, 2009 at 15:22
  • Isn't using distinct just more work to remove duplicates from the subquery? I'd leave it off.
    – Paul Morgan
    Commented Nov 23, 2009 at 18:48
1

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?

Improve this answer
1
  • It still goes back to an IN list, which is problematic. Consider the @AmoBrinkman example or the BT26; these do not require the engine to make a large array. e.g., you could change "where id in" to "where exists" and it should probably work identically, but much faster and "at all" for large volumes.
    – troy
    Commented May 17, 2016 at 17:20
1

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.