Solved

deleting from multiple tables.

Posted on 2014-11-21
13
92 Views
Last Modified: 2014-11-22
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 = ?";
0
Comment
Question by:kadin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 40459027
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
 

Author Comment

by:kadin
ID: 40459043
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40459284
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
Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 40459288
You could of course write an AFTER DELETE Trigger that tidies up for you.
0
 

Author Comment

by:kadin
ID: 40459898
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40459925
Yes because you can not use inner joins, outer joins etc.
0
 

Author Comment

by:kadin
ID: 40460115
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40460117
A Delete trigger would maybe be more efficient?
0
 

Author Comment

by:kadin
ID: 40460118
How would that work? Is it to complex to explain?
0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 40460125
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
 

Author Comment

by:kadin
ID: 40460140
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
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40460146
old is the internal name used by MySql to denote the record that was just deleted.
0
 

Author Comment

by:kadin
ID: 40460151
Thanks. I will try to put something together and test it.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question