How can I delete using INNER JOIN with SQL Server?

ID : 724

viewed : 97

Tags : sqlsql-serversql-server-2008inner-joinsql-deletesql

Top 5 Answer for How can I delete using INNER JOIN with SQL Server?

vote vote

92

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

80

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

75

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

67

Try this:

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

53

It should be:

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

Top 3 video Explaining How can I delete using INNER JOIN with SQL Server?

Related QUESTION?