CODEWITHSUNDEEP
sqlstored-proceduresparent-child
Bee
Bee

Reputation: 3

SQL: delete parent when child does not exist

I have two tables:

tblEmployee 

EmployeeID (PK), Name, Age, Department

tblDesignation 

EmployeeID(FK), Designation, DesignationID

I need help to write a stored procedure to delete an employee record only if it does not have a record in the designation table. (delete employeeID only if there is no designation assigned)

I am not sure if I should use the ON DELETE RESTRICT constraint or NOT EXISTS clause.

Upvotes: 0

Views: 1464

Answers (2)

LifeOfPi
LifeOfPi

Reputation: 625

Well you can have below code for your purpose. Please excuse with syntax

create procedure usp_deleteemployee
as 
begin
    delete from tblEmployee 
    where id not in (select empid from tblDesignation);
end

Or you can try with exist also. Not sure about syntax

DELETE tblEmployee 
FROM tblEmployee 
WHERE NOT EXISTS (SELECT 1 
                  FROM tblDesignation 
                  WHERE tblEmployee.Id = tblDesignation.empid)

Upvotes: 1

Praveen
Praveen

Reputation: 1449

You could try something like below.

DELETE
E
FROM
tblEmployee E
LEFT JOIN tblDesignation D
ON E.EmployeeID = D.EmployeeID
WHERE
D.EmployeeID IS NULL

Hope this Helps!!

Upvotes: 0

Related Questions