How can I delete using INNER JOIN with SQL Server?

I want to **delete** using `INNER JOIN` in **SQL Server 2008**.

But I get this error:

> Msg 156, Level 15, State 1, Line 15
> **Incorrect** syntax near the **keyword** 'INNER'.

My code:

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

Try this:

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

It should be:

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

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)

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'

You need to specify what table you are deleting from. Here is a version with an alias:

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

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

In [SQL Server Management Studio][1] 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

[1]:

[To see links please register here]