Solved

deleting from multiple tables.

Posted on 2014-11-21
13
88 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
  • 6
  • 6
13 Comments
 
LVL 40

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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Date Query 28 44
encyps queries mssql 15 38
Difference in number of minutes between 2 timestamps 16 39
SQL Query resolving a string conversion issue 26 37
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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 this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now