[SOLVED] Delete rows from one table based on query applied to a second table

Delete rows from one table based on query applied to a second table

I have two related SQL tables as follows:

Parent_table

item_id	common_index
id_1	index_1
id_2	index_2
id_3	index_3

Child_table

common_index	sale_value	year
index_1	value_1	year_1
index_2	value_2a	year_1
index_2	value_2b	year_2

and as the item with id_2 has been withdrawn from stock I would like to delete rows from both the tables that ultimately refer to id_2, but without having to explicitly specify what the common_index is. I try something like:

DELETE FROM Child_table
    WHERE common_index IN (
        SELECT common_index FROM Parent_table
            WHERE item_id = 'id_2');

On running the query, it should display the surviving rows in the Child_table (in this case the row involving index_1). But instead, I get the message 'No rows to display'.

On the other hand, when DELETE is replaced by SELECT * it lists the rows from Child_table that are 'earmarked' for being deleted.

And as I also want to simultaneously delete from the Parent_table the row where item_id=2, I tried placing the following query before it:

DELETE FROM Parent_table
    WHERE item_id = 'id_2'

But when that happens I get the message 'near "DELETE": syntax error', as if we are not permitted to use multiple DELETE statements (unlike INSERT). A similar thing happens if you try to place this block after the code for the Child_table.

So how do I get the surviving rows to be displayed? And how to perform both deletions within the same query? Thanks.

Solution

In MySQL, if you want to delete from two tables with one single statement, you need to:

specify the tables right after the DELETE keyword,
apply a JOIN operation between the two tables
add the conditions of deletion

DELETE p, c
FROM       Parent_table p
INNER JOIN Child_table  c
        ON p.common_index = c.common_index
       AND p.item_id = 'id_2';

Check the demo here.