How to delete multiple rows from different tables in single query

Question

0.00/5 (No votes)

See more:

Hi,

I am facing an issue while deleting row from multiple table in a single query.
My tables are : Event - Parent Table which have EventID as an primary key in it.
The other tables which contains the EventID as foreign key are :

Coordinator
Event_Attendee_Organizer
Event_Organizer
Invitation
Slot

I want to delete the particular rows from all the tables where eventid and organizerid matches. The OrganizerID is present in Event_Attendee_Organizer and Event_Organizer table.

Please help me to solve this issue.

Thanks in advance...

Posted 8-Jul-12 18:50pm

rp786

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Prasad_Kulkarni · Answer 1 · 2012-07-08T20:10:00

Try this:

SQL

DELETE t1,t2,t3 FROM table1 as t1
JOIN table2 as t2 ON t2.ID = t1.ID
JOIN table3 as t3 ON t3.ID = t1.ID

Your eventID in all table will make it work.

For deleting records from multiple tables:
You could define Foreign Key constraints (which you have defined as EventID) for the other tables that reference the master table's ID with ON DELETE CASCADE.
This would cause the related rows in those tables to be deleted. This is the least programming model, is part of the SQL standard, etc. Just be sure that you think through the issues with always deleting the referencing rows.
Refer for more details: http://msdn.microsoft.com/en-us/library/ms174979.aspx[^]

You could create a delete trigger on the table where you delete the master row for the ID that would then contain code to delete related rows
from other tables.
You still have to write the code for this one, but it hides inside the trigger and is used by any deletes from the table as long as the trigger is not disabled.
Refer for more details: http://msdn.microsoft.com/en-us/library/ms189799.aspx[^]

__TR__ · Answer 2 · 2012-07-08T19:03:00

Try this

 BEGIN TRY
   BEGIN TRANSACTION

   DELETE EAO
   FROM Event_Attendee_Organizer EAO
   INNER JOIN [Event] E ON EAO.OrganizerID = E.EventID


   DELETE EO
   FROM Event_Organizer  EO
   INNER JOIN [Event] E ON EO.OrganizerID = E.EventID

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
   ROLLBACK TRANSACTION
END CATCH

seravan · Answer 3 · 2012-07-08T19:10:00

Solution 2

Just do a cascade delete for this one.

Posted 8-Jul-12 19:10pm

seravan

How to delete multiple rows from different tables in single query

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0