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

Question

Here are two tables:

Table 1

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

table 2

 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, i.e.

 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 only SQL statement that I can use to remove rows from table1, where order_num in table2 is between 518 and 520?

Answer 1

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

Answer 2

 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 of FROM , and the request was modified according to Andriy M comments.

Answer 3

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; 

Answer 4

use DELETE with a subquery:

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