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'


@Rafael Xavier 2020-09-01 18:28:45

You could even do a sub-query. Like this code bellow:

    SELECT user_id FROM Employee WHERE Company = '1' AND Date = '2013-05-06'

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

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'

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

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

@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:…?

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

@Himanshu Jansari 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.* ,
FROM table1 
INNER JOIN table2 ON where ='given_id'

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

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

Another way using CTE.

;WITH cte 
     AS (SELECT * 
         FROM   workrecord2 w 
                        FROM   employee e 
                        WHERE  employeerun = employeeno 
                               AND company = '1' 
                               AND date = '2013-05-06')) 

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:

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

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

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

@Prajakta Kale 2020-01-17 07:35:33

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

@Geoff Griswald 2020-10-05 13:18:03

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.

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

The answer from @Devart is also Standard SQL though incomplete, should look more like this:

  FROM WorkRecord2
                   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)

OUTPUT deleted.ProfileId INTO @ProfileId
WHERE Email = @email

DELETE FROM UserProfiles    
WHERE Id = (Select Id FROM @ProfileId)

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

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

@dandev91 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'

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

@Geoff Griswald 2020-10-05 13:19:03

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

@Himanshu Jansari 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';

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

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

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

@Himanshu Jansari 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

49 Answered Questions

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

30 Answered Questions

35 Answered Questions

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

31 Answered Questions

27 Answered Questions

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

11 Answered Questions

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

41 Answered Questions

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

8 Answered Questions

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

Sponsored Content