提问



我想在 SQL Server 2008 中使用INNER JOIN删除。


但我得到这个错误:



  消息156,15级,状态1,行15

  关键字INNER附近的语法不正确。



我的代码:


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

最佳参考


您需要指定要删除的表,这是一个带别名的版本:


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

其它参考1


只需在DELETEFROM之间添加要删除记录的表的名称,因为我们必须指定要删除的表。同时删除ORDER BY子句,因为删除记录时无需订购。


所以你的最终查询应该是这样的:


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

其它参考2


尝试这个:


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

其它参考3


这可能对你有所帮助 -


DELETE FROM dbo.WorkRecord2 
WHERE EmployeeRun IN (
    SELECT e.EmployeeNo
    FROM dbo.Employee e
    WHERE ...
)


或试试这个 -


DELETE FROM dbo.WorkRecord2 
WHERE EXISTS(
    SELECT 1
    FROM dbo.Employee e
    WHERE EmployeeRun = e.EmployeeNo
        AND ....
)

其它参考4


它应该是:


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

其它参考5


试试这个查询:


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

其它参考6


这个版本应该有效


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

其它参考7


在SQL Server Management Studio中,我可以轻松创建SELECT查询。


SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.Postcode
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf


我可以执行它,并显示所有联系人。


现在将SELECT更改为DELETE:


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


您将在SELECT语句中看到的所有记录都将被删除。


您甚至可以使用相同的过程创建更难的内部联接,例如:


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

其它参考8


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

其它参考9


你没有为CompanyDate指定表格,你可能想要解决这个问题。


使用MERGE的标准SQL:


MERGE WorkRecord2 T
   USING Employee S
      ON T.EmployeeRun = S.EmployeeNo
         AND Company = '1'
         AND Date = '2013-05-06'
WHEN MATCHED THEN DELETE;


@Devart的答案也是标准SQL虽然不完整,但看起来应该更像这样:


DELETE 
  FROM WorkRecord2
  WHERE EXISTS ( SELECT *
                   FROM Employee S
                  WHERE S.EmployeeNo = WorkRecord2.EmployeeRun
                        AND Company = '1'
                        AND Date = '2013-05-06' );


关于上述内容的重要一点是很明显删除是针对单个表,如第二个示例中强制要求标量子查询。


对我来说,各种专有的语法答案更难阅读和理解。我想@frans eilering在答案中最好地描述了心态,即编写代码的人并不一定关心将阅读和维护代码的人。

其它参考10


使用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 


注意:当你想要delete时,我们不能在CTE内使用JOIN

其它参考11


这是一个简单的查询,一次从两个表中删除记录。


DELETE table1.* ,
       table2.* 
FROM table1 
INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'

其它参考12


试试这个,它可能会有所帮助


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

其它参考13


这是我的SQL Server版本


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)

其它参考14


这是我目前用于删除甚至更新的内容:


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