clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3364508 fiddles created (36229 in the last week).

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
 hidden batch(es)


BEGIN TRANSACTION; -- create temporary table create table #deletedRecords (employeeId int); -- INSERT INTO #deletedRecords SELECT e.EmployeeNo FROM WorkRecord2 w INNER JOIN Employee e ON e.EmployeeNo = w.EmployeeRun AND w.Company = 1 AND w.date = '2013-05-06'; -- delete from WorkRecord2 DELETE w FROM WorkRecord2 w INNER JOIN #deletedRecords d ON w.EmployeeRun = d.employeeId; -- delete from Employee using exists DELETE FROM Employee WHERE EXISTS (SELECT 1 FROM #deletedRecords d WHERE d.employeeId = EmployeeNo); -- drop temporary table DROP TABLE #deletedRecords; COMMIT TRANSACTION;
EmployeeNo
1
2
 hidden batch(es)


SELECT * FROM WorkRecord2
id EmployeeRun Company date
1 1 1 2013-05-06
2 2 1 2013-05-06
3 3 1 2013-05-07
 hidden batch(es)


SELECT * FROM Employee
EmployeeNo EmpName
1 a
2 b
3 c
 hidden batch(es)