By nettoon493


2013-05-10 11:38:21 8 Comments

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'

15 comments

@viraj sharma 2018-04-10 07:26:59

Try this, it might help

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

@default locale 2018-04-10 07:48:12

How is it different from the accepted answer: stackoverflow.com/questions/16481379/…?

@Joshua Burns 2019-04-05 20:09:53

This answer uses explicit table naming rather than aliasing, making it asier for those less experienced to read /catch on to what is going on.

@hims056 2019-04-29 11:22:15

@JoshuaBurns: Still exact duplicate of my answer.

@Ady 2017-05-10 13:08:53

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'

@D-Money 2018-07-10 21:43:45

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.

@PPJN 2017-02-22 14:50:17

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'

@Pரதீப் 2016-09-10 14:59:45

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.

@Taryn 2013-05-10 11:43:31

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'

@Pragnesh Rupapara 2013-09-20 10:39:23

if you want to delete from both the table use DELETE w,e FROM WorkRecord2 w INNER JOIN Employee e ON EmployeeRun=EmployeeNo Where Company = '1' AND Date = '2013-05-06'

@Taryn 2013-11-19 15:16:24

@PragneshMca That syntax will not work for SQL Server.

@oabarca 2014-05-21 14:43:07

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

@Taryn 2014-05-27 14:12:19

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

@Mathieu Rodic 2014-06-01 20:28:21

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

@Verena Haunschmid 2016-08-24 05:56:32

@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 Prabaharan 2016-09-26 08:06:16

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

@stomy 2018-04-09 16:04:19

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

@Shahryar Saljoughi 2018-04-29 15:56:25

what is w in front of delete keyword?

@Taryn 2018-04-29 15:57:33

@ShahryarSaljoughi that is the alias for the WorkRecord2 table.

@onedaywhen 2016-05-19 13:22:19

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.

@ozzy432836 2016-03-09 09:28:26

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)

@frans eilering 2015-06-01 21:08:22

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 he same procedure, for example:

DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf

@Dhanraj Mittal 2015-04-28 16:00:54

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

@Nima Derakhshanjan 2015-04-28 16:20:12

fine but wish to explain a little about your code.

@Ali 2014-07-16 10:16:43

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

@Stealth Rabbi 2015-03-17 14:35:45

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

@fila 2016-12-20 05:45:58

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

@yoginder bagga 2014-08-17 08:15:09

It should be:

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

@AustinTX 2014-08-12 19:56:15

This version should works

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

@Behrouz Bakhtiari 2013-05-10 17:37:22

Try this:

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

@Den 2015-05-20 08:26:00

Doesn't work for me.

@Devart 2013-05-10 12:12:23

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

@Chris Moschini 2015-09-29 14:18:32

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)

@hims056 2013-05-10 12:07:59

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

@matwonk 2018-12-21 18:55:38

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

@hims056 2018-12-22 04:18:11

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

@hims056 2019-05-02 10:52:18

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

Related Questions

Sponsored Content

43 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

27 Answered Questions

27 Answered Questions

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

3 Answered Questions

7 Answered Questions

[SOLVED] How to rename a table in SQL Server?

10 Answered Questions

[SOLVED] Update a table using JOIN in SQL Server?

11 Answered Questions

[SOLVED] SQL Server: How to Join to first row

37 Answered Questions

Sponsored Content