Solved

deleting from multiple tables.

Posted on 2014-11-21
13
86 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

706 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

22 Experts available now in Live!

Get 1:1 Help Now