How can I 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

User · Answer

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

User · Answer

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

User · Answer

DELETE a FROM WorkRecord2 a         INNER JOIN Employee b         ON a EmployeeRun   b EmployeeNo         Where a Company    1          AND a Date    2013-05-06

User · Answer

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

User · Answer

This version should work  DELETE WorkRecord2 FROM WorkRecord2  INNER JOIN Employee ON EmployeeRun EmployeeNo Where Company    1  AND Date    2013-05-06

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

It should be   DELETE zpost  FROM zpost  INNER JOIN zcomment ON  zpost zpostid   zcomment zpostid  WHERE zcomment icomment    first

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How can I delete using INNER JOIN with SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to inner-join

Examples related to sql-delete