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'

Blanche

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

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

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

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

Disburse

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

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

@user2070775 in SQL Server, you can use transactions and pseudo-tables, as shown in #784226 – Garlic

@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 :) – Bordelaise

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

No need to mention target table more than once. See learn.microsoft.com example 3 – Cursive

what is w in front of delete keyword? – Squilgee

@ShahryarSaljoughi that is the alias for the WorkRecord2 table. – Disburse

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

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. – Insure

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

I know this is old, but wanted to throw in here that SQL Server allows you to use the "into" statement, to dump the rows deleted into a table (actual, temp or variable works for this). You can then refer to it for your subsequent delete. – Thrust

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

Renelle

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

@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. – Renelle

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

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... – Bohaty

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

Bonanno

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) – Tusche

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

Answer 4

Try this:

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

Hypocaust

Answer 5

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

Subscription

I like this the best. What people are missing is you have to use the table name between DELETE AND FROM statement or use an alias you can't just have DELETE FROM Contact INNER JOIN... – Diminutive

Answer 6

It should be:

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

Whistling

Answer 7

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

Meit

Answer 8

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

Lacteal

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

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

Answer 9

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.

Refrigerate

Answer 10

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.

Ctenophore

Answer 11

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'

Reinaldo

Answer 12

Delete multiple table data using transaction block, table variable and JOIN.

BEGIN TRANSACTION;

   declare @deletedIds table ( id int );
   
   DELETE w
   output deleted.EmployeeRun into @deletedIds
   FROM WorkRecord2 w
   INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';

   DELETE e
   FROM Employee as e
   INNER JOIN @deletedIds as d
           ON d.id = e.EmployeeNo;
COMMIT TRANSACTION;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=43330dda6f1b71b8ec4172a24d5b6921

Delete multiple table data with temporary table and JOIN. Drop temporary table after deletion.

BEGIN TRANSACTION;

    -- create temporary table
    create table #deletedRecords (employeeId int);
    
    -- INSERT INTO #deletedRecords
    SELECT e.EmployeeNo
    FROM WorkRecord2 w
    INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';
          
    -- delete from WorkRecord2
    DELETE w
    FROM WorkRecord2 w
    INNER JOIN #deletedRecords d
           ON w.EmployeeRun = d.employeeId;
           
    -- delete from Employee using exists
    DELETE 
    FROM Employee
    WHERE EXISTS (SELECT 1
                  FROM #deletedRecords d
                  WHERE d.employeeId = EmployeeNo);
                  
    -- drop temporary table
    DROP TABLE #deletedRecords;

COMMIT TRANSACTION;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=d52c6c1ed91669d68fcc6bc91cb32d78

Alternative way to create temporary tables by using SELECT INTO

BEGIN TRANSACTION;

    SELECT  e.EmployeeNo employeeId 
    INTO #deletedRecords
    FROM WorkRecord2 w
    INNER JOIN Employee e
           ON e.EmployeeNo = w.EmployeeRun
          AND w.Company = 1
          AND w.date = '2013-05-06';
          
    -- delete from WorkRecord2
    DELETE w
    FROM WorkRecord2 w
    INNER JOIN #deletedRecords d
           ON w.EmployeeRun = d.employeeId;
           
    -- delete from Employee using exists
    DELETE 
    FROM Employee
    WHERE EXISTS (SELECT 1
                  FROM #deletedRecords d
                  WHERE d.employeeId = EmployeeNo);
                  
    -- drop temporary table
    DROP TABLE #deletedRecords;

COMMIT TRANSACTION;

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=0f02f05616ce5b4dcc8fc67c6cf1e640

Remove a single table data using JOIN

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
        ON e.EmployeeNo = w.EmployeeRun
       AND w.Company = 1
       AND w.date = '2013-05-06'

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=84a60d1368556a8837281df36579334a

Delete single table data using CTE

WITH cte AS (
     SELECT w.EmployeeRun
     FROM WorkRecord2 w
     WHERE EXISTS (SELECT 1
                   FROM Employee 
                   WHERE EmployeeNo = w.EmployeeRun)
         AND w.Company = 1
         AND w.date = '2013-05-06'
)
DELETE
FROM cte

Please check from url https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=6590007b3c8c2ffad5563bd86606c5b1

Use ON CASCADE DELETE during foreign key creation in child table. If remove parent table data then corresponding child table data is automatically deleted.

Autonomy

Answer 13

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)

Basia

Answer 14

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'

Retroflex

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. – Rawson