我想在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'
我想在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 WorkRecord2
FROM Employee
Where EmployeeRun=EmployeeNo
And Company = '1'
AND 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
注意:当您想要删除时,我们不能在CTE中使用JOIN。
以下是我当前用于删除甚至更新的内容:
DELETE w
FROM WorkRecord2 w,
Employee e
WHERE w.EmployeeRun = e.EmployeeNo
AND w.Company = '1'
AND w.Date = '2013-05-06'
您没有指定“公司”和“日期”的表,您可能想解决这个问题。
使用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 eilling的回答中最好地描述了的心态,即编写代码的人不一定关心将阅读和维护代码的人。
这是一次从两个表中删除记录的简单查询。
DELETE table1.* ,
table2.*
FROM table1
INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'
这是我的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)