0

hi to all, I'm new in mysql and I have a problem regarding deleting rows. I have 38 tables in database and I want to delete rows in all tables with specific ID that is executed in just 1 click.

For example, I executed ID number 1 then it will delete all rows in all table with an ID Number 1.

It seems my current query is horrible. Ex.

1. query(DELETE table1 with ID=1)
2. query(DELETE table2 with ID=1)
3. query(DELETE table3 with ID=1)
4. query(DELETE table4 with ID=1)
5. query(DELETE table5 with ID=1)

Thank you so much for your help :)

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by alfredferg
0

You can create a trigger that will automtically delete from the child tables when the corresponding id is deleted from the parent table, as demonstrated below.

CREATE OR REPLACE TRIGGER supplier_bir
      BEFORE DELETE ON supplier
      FOR EACH ROW
    BEGIN
      DELETE FROM supdealer WHERE sup_id = :OLD.id;
      DELETE FROM suphour WHERE sup_id = :OLD.id;
      DELETE FROM supcontact WHERE sup_id = :OLD.id;
    END supplier_bir;

Hopefully it is clear..

0

Hi tomato.pgn,

Thank you so much for your quick reply. :) Is it possible to eliminate the repeating query "DELETE" with the specific table? For example, is it possible to search all tables on a database and search for each table with a field ID executed? Because I have other tables that is no field name ID.

Thanks again!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.