5 {{ upvoteCount | shortNum }}
5 {{ upvoteCount | shortNum }}

delete records from 3 tables simultaneously in mysql

by Jilawatan
3 replies
Hello Guyzz,,

I need a help about to delete records simultaneously from 3 different tables in mysql

Here is my 3 tables structure with columns:

Category:
  • catID
  • cat_name

Artists:
  • artID
  • art_name
  • catID

Song:
  • songID
  • s_name
  • artID

What I am trying to do is, when I delete a record from 'category' table, it should delete those artists which have the same catID and then when an artist will be deleted, the songs under that artist which is also going to be deleted, should also deleted.

I can do this for two tables, like when I delete a record from cats table, it will auto delete a record from artists table, but i am confuse in the 3rd table that how can i do this in a single query.

I want to do this all in a single query. I can do this with individuals queries, but like professionals, i want to get all this done in a single query.

Please help me out in learning this.


Thanks in advance
#delete #mysql #records #simultaneously #tables
  • Profile picture of the author webpro4hire
    Try this:

    DELETE A, S
    FROM
    Artists as A,
    INNER JOIN Category as C ON A.catID = C.catID
    INNER JOIN Song as S ON A.artID = S.artID
    WHERE
    A.artID = 1


    the "where" clause is an artist ID.

    warning: backup your database BEFORE attempting any sql commands. better be safe than sorry.

    Cheers,
    WP4H
    {{ DiscussionBoard.errors[3174220].message }}
  • Profile picture of the author SteveJohnson
    Or you can employ DELETE...USING:
    Code:
    DELETE FROM Category c, Artists a, Song s
USING Category c, Artists a, Song s
WHERE c.catID = 'xxxxx' AND a.catID = c.catID AND s.artID = c.artID
    Lots of ways to skin this cat.
    Signature

    The 2nd Amendment, 1789 - The Original Homeland Security.

    Gun control means never having to say, "I missed you."

    {{ DiscussionBoard.errors[3175451].message }}
  • Profile picture of the author h_al
    Add foreign key constraint ON DELETE CASCADE.
    {{ DiscussionBoard.errors[3177257].message }}

Trending Topics