Delete child record with one to many relation

I have Master table "Employer" and child table "EmpDetails"
Table "Employer" have following columns


EmployeeID , EmploymentVersionID,Name and Score.

Child Table "EmpDetails" have the following column
EmpDetailsID,EmployeeID,EmploymentVersionID,Address,Phone,email.

Now i want to delete the master table record and associate with child record (its one to many relationship).

I amn not sure how to loop and delete all the child record associate with master recrod, any help much appreciated.
Sha1395Asked:
Who is Participating?
 
ste5anSenior DeveloperCommented:
You don't "loop". In T-SQL we use sets and thus set-based approaches. To delete all child rows you specify an appropriate condition:

DECLARE @EmployeeID INT:
SET @EmployeeID = 123;

BEGIN TRANSACTION;

DELETE FROM EmpDetails
WHERE EmployeeID = @EmployeeID;

DELETE FROM Employer
WHERE EmployeeID = @EmployeeID;

COMMIT TRANSACTION;

Open in new window

 

This will delete all rows in the child table with the same EmployeeID. Then the master row.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.