如何在 SQL Server 中使用 INNER JOIN 删除?

我想在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'

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

因此,您的最终查询应如下所示:

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

可能对您有帮助 -

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

尝试这个:

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

它应该是:

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

这个版本应该工作

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

在 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

试试这个查询:

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';
DELETE a FROM WorkRecord2 a 
       INNER JOIN Employee b 
       ON a.EmployeeRun = b.EmployeeNo 
       Where a.Company = '1' 
       AND a.Date = '2013-05-06'

另一种使用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