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');

WITH cte AS ( SELECT w.EmployeeRun FROM WorkRecord2 w WHERE EXISTS (SELECT 1 FROM Employee WHERE EmployeeNo = w.EmployeeRun) AND w.Company = 1 AND w.date = '2013-05-06' ) DELETE FROM cte

SELECT * FROM WorkRecord2