CODEWITHSUNDEEP
mysqlsql
user784637
user784637

Reputation: 16092

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

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?

Upvotes: 12

Views: 61580

Answers (5)

jafarbtech
jafarbtech

Reputation: 7015

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;

Upvotes: 1

Arnon Rotem-Gal-Oz
Arnon Rotem-Gal-Oz

Reputation: 25909

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)

Upvotes: 2

bk00041
bk00041

Reputation: 51

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;

Upvotes: 5

Elias Hossain
Elias Hossain

Reputation: 4469

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.

Upvotes: 8

Zohaib
Zohaib

Reputation: 7116

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

Upvotes: 26

Related Questions