By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table Employee (EmployeeNo int, EmpName varchar(50));
insert into Employee values (1, 'a'), (2, 'b'), (3, 'c');
create table WorkRecord2 (id int, EmployeeRun int, Company int, [date] date);
insert into WorkRecord2 values (1, 1, 1, '2013-05-06'), (2, 2, 1, '2013-05-06')
, (3, 3, 1, '2013-05-07');
6 rows affected
BEGIN TRANSACTION;
declare @deletedIds table ( id int );
DELETE w
output deleted.EmployeeRun into @deletedIds
FROM WorkRecord2 w
INNER JOIN Employee e
ON e.EmployeeNo = w.EmployeeRun
AND w.Company = 1
AND w.date = '2013-05-06';
DELETE e
FROM Employee as e
INNER JOIN @deletedIds as d
ON d.id = e.EmployeeNo;
COMMIT TRANSACTION;
4 rows affected
SELECT *
FROM WorkRecord2
| id | EmployeeRun | Company | date |
|---|---|---|---|
| 3 | 3 | 1 | 2013-05-07 |
SELECT *
FROM Employee
| EmployeeNo | EmpName |
|---|---|
| 3 | c |