Click here to Skip to main content
15,076,465 members
Please Sign up or sign in to vote.
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
Updated 19-Feb-18 21:28pm

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[^]
   
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
   
Comments
kav@94 20-Feb-18 2:56am
   
Hi thanks for your quick reply i want to do this delete operation using query not through stored procedure

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900