Performing a deletion in 3 related tables

Question

Table1	Table2	Table3
Req_id	Req_id	env_id=1
	env_id(FK)	env_id=2

Ignoring other fields...Each req_id in Table 2 has an env_id and it gets this env_id from Table 3.

If a req_id in Table 2 has a record with EVERY env_id in Table 3, I want to delete that req_id from Table 1.

Since the env_id's in Table 3 will be different in every environment where I will run this script. So, Delete script needs to be written dynamically. Thank you for any help.

   BEGIN
       FOR a
          IN (SELECT ENV_ID FROM Table3 t3)
            LOOP
              BEGIN
               DELETE FROM Table1 t1 WHERE t1.ID IN
               (SELECT t1.ID FROM Table1 t1
                INNER JOIN Table2 t2 ON t2.REQ_ID = t1.REQ_ID
                INNER JOIN Table3 t3 ON t3.ENV_ID = t2.ENV_ID
                AND t2.ENV_ID=a.ENV_ID)
              END;
           END LOOP;
      COMMIT;
    END;

Does this respond to your question ? https://www.mysqltutorial.org/mysql-on-delete-cascade/ — Ichinator, Jun 15 '21 at 14:56
And what have you tried to achieve this? Where are you stuck? — Nico Haase, Jun 15 '21 at 15:18
Title, message text, code you posted - all suggest that you want to delete a row from **table1**. Comment (you posted an hour ago) says that you want to delete rows from **table2**. What do you really want? Sample data would help. Also, as far as I understood the "model" you posted, you can't delete anything from table2 nor table1 as long as child rows exist in table3. — Littlefoot, Jun 15 '21 at 16:24
Sorry for typo, i have edited my post. If a record in table 2 has a record with ALL env_id in table 3, I want to delete the records in table 1 that they are related to table2. — Ali Iskender, Jun 15 '21 at 20:37

score 0 · Answer 1 · answered Jun 16 '21 at 01:40

If a req_id in Table 2 has a record with EVERY env_id in Table 3, I want to delete that req_id from Table 1.

I believe you're describing relational division here. In sql, it's not so obvious how to write/read queries for this operation so there are a lot of questions/answers in stackoverflow and elsewhere.

Some examples:

The rest of your requirements should just be joining through to the correct tables for the delete.

Performing a deletion in 3 related tables

1 Answers1