update How to Delete using INNER JOIN with SQL Server?




sql update inner join (12)

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

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'

It should be:

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

Another way 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.


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'

Try this, it might help

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

You don't specify the tables for Company and Date, 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, 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.


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

Here's what I currently use for deleting or even, updating:

DELETE FROM      w
FROM             WorkRecord2   w,
                 Employee      e
WHERE            w.EmployeeRun = e.EmployeeNo
             AND w.Company = '1' 
             AND w.Date = '2013-05-06'

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)

Try this:

DELETE FROM WorkRecord2 
       FROM Employee 
Where EmployeeRun=EmployeeNo
      And 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 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';




sql-delete