How to Delete using INNER JOIN with SQL Server?

Question

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'

Answer 1

If you want to delete related data from more than one table in one you can use a query structure as below:

delete d.*,r.*,a.* from notifications_data d inner join notification_recipient_details r on d.notifications_data_id=r.notifications_data_id inner join notifications_audit a on d.notifications_data_id = a.notifications_data_id

The above query works perfectly and deletes data from three tables

Answer 2

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'

Answer 3

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 he same procedure, for example:

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

Answer 4

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