How can I delete using INNER JOIN with SQL Server?

You don't specify the tables for `Company` and `Date`, and you might want to fix that.

Standard SQL using `MERGE`:

MERGE WorkRecord2 T
USING Employee S
ON T.EmployeeRun = S.EmployeeNo
AND Company = '1'
AND Date = '2013-05-06'
WHEN MATCHED THEN DELETE;

[The answer from Devart][1] is also *standard* SQL, though incomplete. It should look more like this:

DELETE
FROM WorkRecord2
WHERE EXISTS ( SELECT *
FROM Employee S
WHERE S.EmployeeNo = WorkRecord2.EmployeeRun
AND Company = '1'
AND Date = '2013-05-06' );

The important thing to note about the above is it is clear the delete is targeting a single table, as enforced in the second example by requiring a scalar subquery.

For me, the various proprietary syntax answers are harder to read and understand. I guess the mindset for is best described in [the answer by frans eilering][2], i.e. the person writing the code doesn't necessarily care about the person who will read and maintain the code.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

Another way is using `CTE`:

;WITH cte
AS (SELECT *
FROM workrecord2 w
WHERE EXISTS (SELECT 1
FROM employee e
WHERE employeerun = employeeno
AND company = '1'
AND date = '2013-05-06'))
DELETE FROM cte

**Note:** We cannot use `JOIN` inside `CTE` when you want to `delete`.

You could even do a sub-query. Like this code below:

DELETE FROM users WHERE id IN(
SELECT user_id FROM Employee WHERE Company = '1' AND Date = '2013-05-06'
)

This is a simple query to delete the records from two table at a time.


DELETE table1.* ,
table2.*
FROM table1
INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'

Delete multiple table data using transaction block, table variable and JOIN.

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;

Please check from url

[To see links please register here]

Delete multiple table data with temporary table and JOIN. Drop temporary table after deletion.

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;

Please check from url

[To see links please register here]

Alternative way to create temporary tables by using SELECT INTO

BEGIN TRANSACTION;

SELECT e.EmployeeNo employeeId
INTO #deletedRecords
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;

Please check from url

[To see links please register here]

Remove a single table data using JOIN

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
ON e.EmployeeNo = w.EmployeeRun
AND w.Company = 1
AND w.date = '2013-05-06'

Please check from url

[To see links please register here]

Delete single table data using CTE

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

Please check from url

[To see links please register here]

Use **ON CASCADE DELETE** during foreign key creation in child table. If remove parent table data then corresponding child table data is automatically deleted.