How to delete rows from table sql

Question

I want to delete specific rows from 8 tables. My problem is that the rows are connected with foreign key. How can I delete all the data that connected to the specific rows that I want to delete? My tables include definition tables (like id, name ,max value, min value...), data tables (like id, user_id, definition_id,....) and history tables (save every change in data table).

I thought to use delete on cascade command but I could not find a way to use it.

You can do it with 8 delete statements, one for each table, in the correct order so that FK constraints are not violated. — Mikael Eriksson, Jul 29 '12 at 12:12

score 4 · Accepted Answer · answered Jul 29 '12 at 12:20

DELETE CASCADE is an attribute of the foreign key constraint. Unfortunately it's not something you can use as an option with a DELETE statement (which would be really cool actually)

If your foreign keys have not been declared as cascading you need to "work your way up".

Unfortunately you did not show us your real table structure so let's assume something like this:

main_table (main_id)
   child_one (id, main_id)
     child_two (id, id_one)
       child_three (id, id_two)

(I know you said 8 tables, but for the sake of the demonstration I shortened it a bit, but that doesn't change the underlying "strategy")

Assuming you want to delete the row with main_id = 42 from `main_table:

You first need to delete the rows from child_three using something like this:

delete from child_three
where id_two in (select id 
                 from child_two 
                 where id_one in (select id
                                  from child_one 
                                  where main_id = 42);

Then delete the rows from child_two:

delete from child_two
where id_one in (select id
                 from child_one 
                 where main_id = 42);

Then child_one:

delete from child_one
where main_id = 42;

And finally the main table:

delete from main_table
where id = 42;

Some SQL clients can actually generate those statements for you. I don't know if SQL Developer can though.

score 0 · Answer 2 · edited May 23 '17 at 12:22

I assume that you use InnoDB Engine, since you are talking about foreign keys,

Easier will be to define properly the table so that a deletion will act as a cascade deletion.

     CONSTRAINT `myForeignKey` FOREIGN KEY (`typeId`)
        REFERENCES `types` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

Here is alink with a proper create table statement:

How do I use on delete cascade in mysql?

How to delete rows from table sql

2 Answers2