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'

Solution 1

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'

Solution 2

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

Solution 3

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

Solution 4

Try this:

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

Solution 5

It should be:

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

View more solutions

Comments

[Pondlife]

Example C in the documentation shows how to use DELETE with a join

[reggaeguitar]

Example C uses a cursor and a bunch of extraneous stuff too

[fish-404]

Example D Using joins and subqueries to data in one table to delete rows in another table may be the correct one.

[oabarca]

@bluefeet could you provide the right syntax for SQL Server for deleting from both tables?

[Taryn]

@user2070775 In SQL Server to delete from 2 tables you need to use 2 separate statements.

[Mathieu Rodic]

@user2070775 in SQL Server, you can use transactions and pseudo-tables, as shown in stackoverflow.com/questions/783726/…

[Stealth Rabbi]

I'm pretty sure a DELETE can only specify one table. This doesn't work for me.

[Chris Moschini]

This is the only answer that works on Sql Server. Just build your query like select Id from... join ... join etc then wrap it as a subquery and do a delete from (table) where Id in (subquery)

[Verena Haunschmid]

@MathieuRodic thanks for sharing. In my setup if I delete from the 2 tables separately I don't really know anymore which rows to delete from the 2nd table so this will help :)

[Pradeep Kumar]

"DELETE FROM table1 INNER JOIN table2 ON xyz". Will this query delete from table1 alone or from both table1 and table2?

[dandev91]

I believe you can specify multiple tables for deletion in mySQL, but not SQL Server (which the question asks).

[stomy]

No need to mention target table more than once. See docs.microsoft.com example 3

[Shahryar Saljoughi]

what is w in front of delete keyword?

[Taryn]

@ShahryarSaljoughi that is the alias for the WorkRecord2 table.

[Darren Griffith]

This question is for SQL Server. You cannot delete from two tables in one statement in SQL Server. My understanding is this can be done in mysql and MS Access.

[TroySteven]

This one works on SQL Server if you only intend to delete from the first table.

[Himanshu]

@matwonk: You can delete from second table to if you use second table's name. E.g. using DELETE Employee will delete from Employee's table instead of WorkRecord2 table.

[Himanshu]

@matwonk: Here is an example: 1) Deleting from the first table 2) Deleting from the second table.

[Prajakta Kale]

what should be the query to delete data from both the tables at a time?

[Geoff Griswald]

Build 2 temp tables, containing the rows you want to delete from each table. Run two separate delete statements on each table, one at a time, and join to one of the temp tables each time.

[Geoff Griswald]

This is one of many answers that works on SQL Server. I suggest the accepted answer as the best way to do it.

[Daniel]

Deletion order can matter if you are deleting from a table that has itself as a foreign key (And not using cascading delete.) But I agree in general it doesn't matter...

[NTDLS]

@user2070775 you are 100% right, but with cascade delete foreign keys, the same effect can be accomplished with a single user supplied statement.