Write nested SQL query

Question

I have 2 tables

Table1

ID    Name
--------------------
 1    John Carter
 2    Jack Hammer
 3    John Adams
 4    John Doe
 5    Brian Adams

Table2

ID     ID_FromTable1
-----------------------------
 1        2
 2        3
 3        1
 4        1
 5        1
 6        2
 7        3
 8        1
 9        1
10        5
11        4
12        5
13        4

ID in both tables is the primary key

• ID_FromTable1 is the foreign key pointing to ID of Table1.

Now I do something like this:

SELECT ID 
FROM Table1 
WHERE Name like '%John%'

This will give me the IDs 1, 3, 4.

Now using these IDs, I want to write a query on Table2 to delete all entries where ID_FromTable1 are 1, 3, 4.

Please help me write one single SQL query to get all the IDs from Table1 where Name is 'John' and then using those IDs to delete entries from Table2.

I hope I have made the question clear. Do let me know if you need any clarification.

Answer 1

You can use IN with subquery:

DELETE FROM Table2 
WHERE ID_FromTable1 IN ( SELECT ID 
                         FROM Table1 
                         WHERE Name LIKE '%John%' )

Answer 2

In MySQL you can do it with this join

delete table2
from table2
join table1 on table2.id_fromtable1 = table1.id
WHERE t1.Name like '%John%'