How to delete records in one table based on the values in another table?

Question

Here are two tables:

table1

cm_id   cost
1       6.52
2       16.52
3       2.12
4       7.14
5       19.09
6       11.52
7       0.12

table2

um_id   order_num   name
1       517         tommy
2       518         bobby
3       519         scotty
4       520         faris
5       521         justine
6       522         sadie
7       523         nicole

cm_id and um_id represent the same thing so the cost can be tied to each order number, ie

SELECT table1.cm_id, table1.cost, table2.order_num, table2.order_num
FROM table1, table2
WHERE table1.cm_id=table2.um_id;

What is the single SQL statement I can use to delete rows from table1 where the order_num in table2 is between 518 and 520?

I believe this should [answer your question](http://stackoverflow.com/questions/439750/t-sql-selecting-rows-to-delete-via-joins). Just use a join. — Ryan Lewis, Nov 28 '11 at 05:29
Duplicates http://stackoverflow.com/questions/1590799/delete-all-rows-in-a-table-based-on-another-table — , Mar 19 '16 at 20:05

score 26 · Accepted Answer · answered Nov 28 '11 at 05:26

26

delete 
from table1
where cm_id IN (select um_id from table2 where order_num between 518 and 520)

answered Nov 28 '11 at 05:26

Zohaib

7,026
3
26
35

Why not? What would be efficient? Is there another way? – bornfromanegg Jan 21 '16 at 10:02

Elias Hossain · Answer 2 · 2011-11-28T09:08:33.590

8

DELETE table1
FROM   table1 INNER JOIN table2 ON table1.cm_id = table2.um_id
AND    (table2.order_num BETWEEN 518 AND 520)

--OR

DELETE 
FROM  table1
USING table1 INNER JOIN table2 ON table1.cm_id = table2.um_id
WHERE   (table2.order_num BETWEEN 518 AND 520)

EDIT:

There was a duplicate FROM and query has been changed as per Andriy M comments.

edited Nov 28 '11 at 09:08

answered Nov 28 '11 at 05:39

Elias Hossain

4,410
1
19
33

This would work in SQL Server. [In MySQL](http://dev.mysql.com/doc/refman/5.0/en/delete.html "MySQL :: MySQL 5.0 Reference Manual :: 12.2.2 DELETE Syntax") you should either *a)* remove the first `FROM` or *b)* replace the second `FROM` with `USING`. – Andriy M Nov 28 '11 at 08:24
Also, how do these two queries differ from each other? (I mean, apart from the fact that the second one contains fewer spaces.) – Andriy M Nov 28 '11 at 08:25
Oh, I actually forgot to put WHERE clause to 2nd query, I'm changing accordingly, thanks – Elias Hossain Nov 28 '11 at 08:28
Just to clarify: when I was saying, *‘remove `FROM`… replace `FROM`…’*, I meant just the keywords themselves, not the entire *clauses*. Well, you can see the right syntax in the linked article. – Andriy M Nov 28 '11 at 08:34
Oh, I understand now, so should I edit again? Thanks for your time. – Elias Hossain Nov 28 '11 at 08:38
Yes, I think you should, because the main issue, wrong syntax, is still there. I've already given you the article on the right syntax and I even suggested (in plain words) what particular changes you should make to your queries. You only need to apply the changes. :) – Andriy M Nov 28 '11 at 08:48
@AndriyM, I've changed accordingly, would you please have a look over the changes, thank your for your valuable time. – Elias Hossain Nov 28 '11 at 09:09

score 5 · Answer 3 · answered Jun 27 '13 at 12:47

5

I prefer this way

delete from table1
using table1, table2
where table1.cm_id = table2.um_id
and table2.order_num >= 518
and table2.order_num <= 520;

answered Jun 27 '13 at 12:47

bk00041

51
1
3

score 2 · Answer 4 · edited Sep 07 '19 at 20:14

2

use DELETE with subquery:

DELETE FROM table1 WHERE table1.cm_id IN (SELECT table2.um_id FROM table2 WHERE order_num>=518 and order_num<=520)

edited Sep 07 '19 at 20:14

Andrei Volgin

40,755
6
49
58

answered Nov 28 '11 at 05:34

Arnon Rotem-Gal-Oz

25,469
3
45
68

1

DELETE FROM not DELETE * FROM – Cristiana Chavez Mar 07 '18 at 07:41

score 1 · Answer 5 · answered Nov 02 '19 at 08:22

1

As IN has some performance limitations we can use delete command with simple join query like

delete x from table1 x,table2 y where x.cm_id=y.um_id;

answered Nov 02 '19 at 08:22

jafarbtech

6,842
1
36
55

How to delete records in one table based on the values in another table?

5 Answers5

EDIT: