如何在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 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 ....
)

2013-05-10 12:12:23

其他回答

试试看：

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

2013-05-10 17:37:22

这可能对你有帮助-

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

2013-05-10 12:12:23

您甚至可以执行子查询。喜欢下面的代码：

DELETE FROM users WHERE id IN(
    SELECT user_id FROM Employee WHERE Company = '1' AND Date = '2013-05-06'
)

2020-09-01 18:28:45

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

注意：当您想要删除时，我们不能在CTE中使用JOIN。

2016-09-10 14:59:45

尝试此查询：

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

2014-07-16 10:16:43

如何在SQL Server中使用INNER JOIN进行删除？

推荐文章

最新文章

标签