deleting from multiple tables.

I am trying to delete 1 whole row from each of 13 tables. There may or may not be a row to delete, in some of the tables. My sql below does not work. Do I have to inner join all 13 tables? The example below just has six tables. Thanks.

                                    
"DELETE
FROM one on
INNER JOIN two tw
ON on.id = tw.id
INNER JOIN three th
ON tw.id = th.id
INNER JOIN four fo
ON th.id = fo.id
INNER JOIN five fi
ON fo.id = fi.id
INNER JOIN six si
ON fi.id = si.id
WHERE id = ?";
kadinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
You cannot delete from multiple tables at the same time by joining like you mentioned.
You need to write separate DELETE statements for each table.
0
kadinAuthor Commented:
Thanks for your response.

The google search I did seem to suggest that I could, unless I misunderstood.
http://www.mysqltutorial.org/mysql-delete-statement.aspx

http://stackoverflow.com/questions/3331992/how-to-delete-from-multiple-tables-in-mysql

Do you mean it is impossible in MySQL?
That will mean I have to make 13 separate calls to the database.
0
Neil RussellTechnical Development LeadCommented:
Yes you are correct you can.

BUT ONLY when there is a full relationship that is satisfied between all of the tables in your FROM clause.  I dont think you can when there is a table that does not meet the WHERE condition and I am not sure you use JOINS like that for it either.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Neil RussellTechnical Development LeadCommented:
You could of course write an AFTER DELETE Trigger that tidies up for you.
0
kadinAuthor Commented:
Thanks for your help.
What do you mean by full relationship? All my tables have an id column. For example, I want to delete all the rows with the id= 73. There will be no more than one row from each table with that id number and some tables may not have that id number.

Are you saying, for this to work in one sql statement, all tables must have at least one row with that id in the where clause?
0
Neil RussellTechnical Development LeadCommented:
Yes because you can not use inner joins, outer joins etc.
0
kadinAuthor Commented:
I tried some tests. It looks like your right, I can delete from multiple tables with one statement but only if the table has a row with that (id) to delete.

It seems odd SQL has this limitation. I am a little new at this, does anyone know how I might get this done in one statement? Would stored procedures be able to handle something like this?
0
Neil RussellTechnical Development LeadCommented:
A Delete trigger would maybe be more efficient?
0
kadinAuthor Commented:
How would that work? Is it to complex to explain?
0
Neil RussellTechnical Development LeadCommented:
Something like....

First delete the old trigger if it exists

DROP TRIGGER IF EXISTS table1_delete;

Open in new window


Now recreate it

CREATE TRIGGER table1_delete AFTER DELETE on table1
FOR EACH ROW
BEGIN
DELETE FROM table2
    WHERE table2.id = old.id;

DELETE FROM table3
    WHERE table3.id = old.id;

DELETE FROM table4
    WHERE table4.id = old.id;

DELETE FROM table5
    WHERE table5.id = old.id;

DELETE FROM table6
    WHERE table6.id = old.id;

END

Open in new window


Now whenever you delete a record from Table1 it will cascade the deletions through table[2-6]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kadinAuthor Commented:
How do I get the id number in there? For example, do I just use the word old as you did or am I supposed to replace old with something? By the way thanks for your help.
0
Neil RussellTechnical Development LeadCommented:
old is the internal name used by MySql to denote the record that was just deleted.
0
kadinAuthor Commented:
Thanks. I will try to put something together and test it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.