delete rows in all table in database with specific id

Question

alfredferg 0 Newbie Poster

13 Years Ago

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 :)

php

2 Contributors
2 Replies
129 Views
6 Hours Discussion Span
Latest Post 13 Years Ago Latest Post by alfredferg

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tomato.pgn -6 Posting Whiz in Training · Answer 1 · 2011-04-22T08:38:58+00:00

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..

alfredferg 0 Newbie Poster · Answer 2 · 2011-04-22T08:48:34+00:00

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!