如何使用SQL Server的内部联接删除？

Question

我想在SQL Server2008中使用内部联接进行删除。

但我得到了这个错误：

消息156，级别15，状态1，第15行
关键字“inner”附近的语法不正确。

我的代码：

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

Answer 1

可能这对你有帮助-

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

Answer 2

只需在delete和from之间添加要从其中删除记录的表的名称，因为我们必须指定要删除的表。同时删除order by子句，因为在删除记录时没有任何可排序的内容。

所以您的最终查询应该是这样的：

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

Answer 3

您需要指定要从哪个表中删除。下面是一个带有别名的版本：

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