How to delete data fom multiple tables using single SQL query in SQL server

Question

0.00/5 (No votes)

See more:

I have five tables and if i delete a record in gridview i need to delete all the records rleated to that using sql how can i do this.In these five tables tbl4 is the main table and that id in tbl4 is passed to all the remaining four tables and if i delete a record in gridview all the data related to id 123 should be deleted in remaining four tables how can i do this

SQL

select  * from Tbl1 where PName='123'
select  * from Tbl2 where PName='123'
select  * from Tbl3 where PName='123'
select  * from Tbl4 where ID='123'
select  * from Tbl5 where PName='123'

What I have tried:

i could not get how to achieve this

Posted 19-Feb-18 18:19pm

kav@94

Updated 19-Feb-18 21:28pm

Add a Solution

2 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)

CHill60 · Answer 1 · 2018-02-19T21:29:00

As a delete statement can only delete from one table at a time in SQL, you have to use a stored procedure to delete from multiple tables "at once".

However, it looks as if Pname in tables tbl1, tbl2, tbl3 and tbl5 are foreign keys to Pname on tbl4. If that is the case then you want to do a "cascade delete" - here is one article on the subject in CodeProject CASCADE in SQL Server with Example[^]
...and another from a different source Using DELETE CASCADE Option for Foreign Keys[^]

khanabrar · Answer 2 · 2018-02-19T20:51:00

Hi,
Create a Database store procedure and call the below query and when you are deleting from the gridview pass the id to store procdeure
Like:
Create Procedure p_Delete_records
(id int)
as
begin
DECLARE @ID INT =123;
DELETE from Tbl1 where PName=@ID;
DELETE from Tbl2 where PName=@ID;
DELETE from Tbl3 where PName=@ID;
DELETE from Tbl5 where PName=@ID;
DELETE from Tbl4 where ID=@ID;
end

How to delete data fom multiple tables using single SQL query in SQL server

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0