Delete multiple Records from different table's through Parent Table in SQL


Delete multiple Records from different table's through Parent Table in SQL

In this article i'm going to explain "How to delete multiple records from different tables based on parent table in sql server" as i shown in above image. First we need to create this 3 tables i.e { User Table , Fruit Table and UserFruitMapping Table }.

Create Database : User Table

I'm creating this table in master database

 Create Table User(
 @UserID int Primary Key,
 @Username varchar(50))

//Inserting Records

 Insert into User values('1','John')
 Insert into User values('2','Amir')
 Insert into User values('3','Robert')

Create Database : Fruit Table

I'm using this table to delete multiple records which available in UserFruitMapping Table as i shown in above image output result.

 Create Table Fruit(
 @FruitID int Primary Key,
 @FruitName varchar(50))

//Inserting Records 

 Insert into Fruit values('f1', 'Grapes')
 Insert into Fruit values('f'2', 'Banana')
 Insert into Fruit values('f'3', 'Mango')
 Insert into Fruit values('f4', 'PineApple')
 Insert into Fruit values('f5', 'Sapota')
 Insert into Fruit values('f6', 'Kiwi')
 Insert into Fruit values('f7', 'Apple')

Create Database : UserFruitMapping Table

In this table i'm using FOREIGN KEY WITH DELETE CASCADE.


 Create Table Fruit(
 @UFID int Primary Key,
 @FruitID int,
 @UserID int
 contraint fk_UserFruitMapping_Fruit FOREIGN KEY (FruitID) 
  References Fruit(FruitID) On Delete Cascade
 contraint fk_UserFruitMapping_User FOREIGN KEY (UserID) 
  References User(UserID)
)

//Inserting Records 

 Insert into UserFruitMapping values('uf1',f1','1')
 Insert into UserFruitMapping values('uf2',f2','1')
 Insert into UserFruitMapping values('uf3',f3','1')
 Insert into UserFruitMapping values('uf4',f4','1')
 Insert into UserFruitMapping values('uf5',f5','2')
 Insert into UserFruitMapping values('uf6',f6','2')
 Insert into UserFruitMapping values('uf7',f7','3')

What is FOREIGN KEY ON DELETE CASCADE

In general if the parent table record is having a reference in the child table, we can't delete the record directly. With the use of "ON DELETE CASCADE", when we delete the parent table record, that record and all its child references will be deleted at once.

Create StoredProcedure to Delete Record

Create a simple stored procedure to delete record simply by passing userID.

 create procedure spDeleteFruitRecord( @UserID int )
 As
  Begin
   delete Fruit where FruitID IN (Select FruitID from UserFruitMapping where UserID=@UserID 
  End

Just we need to pass UserID to this StoredProcedure "spDeleteFruitRecord".

 Exec spDeleteFruitRecord 1

you get result as i shown in above image

Tag : sql server
0 Komentar untuk "Delete multiple Records from different table's through Parent Table in SQL"

Back To Top