?
Solved

deleting from multiple tables.

Posted on 2014-11-21
13
Medium Priority
?
93 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

How To Install Bash on Windows 10

Windows’ budding partnership with Canonical has certainly led to some great improvements. One of them being the ability to use Bash on your Windows machine without third party applications! This might be one of the greatest things a cloud engineer in a Windows environment can do!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

743 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