How can I delete using INNER JOIN with SQL Server?

Just add the name of the table between DELETE and FROM from where you want to delete records, because we have to specify the table to delete. Also remove the ORDER BY clause because there is nothing to order while deleting records.

So your final query should be like this:

 DELETE WorkRecord2 FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' AND Date = '2013-05-06'; 

It is possible this will be helpful for you -

DELETE FROM dbo.WorkRecord2 WHERE EmployeeRun IN ( SELECT e.EmployeeNo FROM dbo.Employee e WHERE ... ) 

Or try this -

DELETE FROM dbo.WorkRecord2 WHERE EXISTS( SELECT 1 FROM dbo.Employee e WHERE EmployeeRun = e.EmployeeNo AND .... ) 

Try this:

DELETE FROM WorkRecord2 FROM Employee Where EmployeeRun=EmployeeNo And Company = '1' AND Date = '2013-05-06' 

In SQL Server Management Studio I can easily create a SELECT query:

SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode FROM Contact INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf 

I can execute it, and all my contacts are shown.

Now change the SELECT to a DELETE:

DELETE Contact FROM Contact INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf 

All the records you saw in the SELECT statement will be removed.

You may even create a more difficult inner join with the same procedure, for example:

DELETE FROM Contact INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf 

It should be:

DELETE zpost FROM zpost INNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid) WHERE zcomment.icomment = "first" 

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

Try this query:

DELETE WorkRecord2, Employee FROM WorkRecord2 INNER JOIN Employee ON (tbl_name.EmployeeRun=tbl_name.EmployeeNo) WHERE tbl_name.Company = '1' AND tbl_name.Date = '2013-05-06'; 

Here's what I currently use for deleting or even, updating:

DELETE w FROM WorkRecord2 w, Employee e WHERE w.EmployeeRun = e.EmployeeNo AND w.Company = '1' AND w.Date = '2013-05-06' 

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.

Here is my SQL Server version

DECLARE @ProfileId table(Id bigint) DELETE FROM AspNetUsers OUTPUT deleted.ProfileId INTO @ProfileId WHERE Email = @email DELETE FROM UserProfiles WHERE Id = (Select Id FROM @ProfileId) 

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 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, i.e. the person writing the code doesn't necessarily care about the person who will read and maintain the code.

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 https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=43330dda6f1b71b8ec4172a24d5b6921

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 https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=d52c6c1ed91669d68fcc6bc91cb32d78

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 https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=0f02f05616ce5b4dcc8fc67c6cf1e640

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 https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=84a60d1368556a8837281df36579334a

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 https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=6590007b3c8c2ffad5563bd86606c5b1

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