-2

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.

Prakash_S
  • 77
  • 5
  • 1
    MySQL or SQL-Server? They're dramatically different. – lemon Dec 12 '22 at 16:09
  • I am using some website facility (www.sql-practice.com) that does not say exactly what version is being used, but i suspect that it is MySqL. – Prakash_S Dec 12 '22 at 17:31

1 Answers1

0

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.

lemon
  • 14,875
  • 6
  • 18
  • 38
  • That answer appears to work in a locally-installed version of SQL, but not on the above-mentioned website facility. I think that what led to my problem initially was trying to run the query directly from the above-mentioned website, which others believe to be not functioning exactly in the manner you would expect it to for MySQL behavior. – Prakash_S Dec 17 '22 at 12:49
  • Maybe that facility supports another sql dialect different with respect to MySQL. When you're dealing with sql language in general, make sure to understand what's the dialect you're learning, there are so many out there and deeply different. Having that knowledge will also help you get official references to cross-match the syntax and expected behaviour of statements and functions. – lemon Dec 17 '22 at 13:16