如何在 SQL Server 中使用 INNER JOIN 进行删除？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16481379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Delete using INNER JOIN with SQL Server?
提问by nettoon493
I want to delete using INNER JOINin SQL Server 2008.
我想INNER JOIN在SQL Server 2008 中删除 using 。
But I get this error:
但我收到此错误：
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'INNER'.
消息 156，级别 15，状态 1，第 15 行
关键字“INNER”附近的语法不正确。
My code:
我的代码：
DELETE FROM WorkRecord2
INNER JOIN Employee ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'
回答by Taryn
You need to specify what table you are deleting from, here is a version with an alias:
您需要指定要从中删除的表，这是一个带有别名的版本：
DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'
回答by hims056
Just add the name of the table between DELETEand FROMfrom where you want to delete records because we have to specify the table to delete. Also remove ORDER BYclause because there is nothing to order while deleting records.
只需在要删除记录的位置DELETE和FROM位置之间添加表的名称，因为我们必须指定要删除的表。还删除ORDER BY子句，因为删除记录时没有任何命令。
So your final query should be like this:
所以你的最终查询应该是这样的：
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee
ON EmployeeRun=EmployeeNo
WHERE Company = '1'
AND Date = '2013-05-06';
回答by Devart
Possible this be helpful for you -
可能这对你有帮助 -
DELETE FROM dbo.WorkRecord2
WHERE EmployeeRun IN (
SELECT e.EmployeeNo
FROM dbo.Employee e
WHERE ...
)
Or try this -
或者试试这个——
DELETE FROM dbo.WorkRecord2
WHERE EXISTS(
SELECT 1
FROM dbo.Employee e
WHERE EmployeeRun = e.EmployeeNo
AND ....
)
回答by Behrouz Bakhtiari
Try this:
尝试这个：
DELETE FROM WorkRecord2
FROM Employee
Where EmployeeRun=EmployeeNo
And Company = '1'
AND Date = '2013-05-06'
回答by yoginder bagga
It should be:
它应该是：
DELETE zpost
FROM zpost
INNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid)
WHERE zcomment.icomment = "first"
回答by AustinTX
This version should works
这个版本应该可以用
DELETE WorkRecord2
FROM WorkRecord2
INNER JOIN Employee ON EmployeeRun=EmployeeNo
Where Company = '1' AND Date = '2013-05-06'
回答by frans eilering
In SQL Server Management Studio I can easily create a SELECTquery.
在 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
I can execute it, and all my contacts are shown.
我可以执行它，并显示我的所有联系人。
Now change the SELECTto a DELETE:
现在更改SELECT为 a DELETE：
DELETE Contact
FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
All the records you saw in the SELECTstatement will be removed.
您在SELECT声明中看到的所有记录都将被删除。
You may even create a more difficult inner join with he same procedure, for example:
您甚至可以使用相同的过程创建更困难的内部连接，例如：
DELETE FROM Contact
INNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf
INNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf
回答by Dhanraj Mittal
DELETE a FROM WorkRecord2 a
INNER JOIN Employee b
ON a.EmployeeRun = b.EmployeeNo
Where a.Company = '1'
AND a.Date = '2013-05-06'
回答by Ali
Try this query :
试试这个查询：
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';
回答by P?????
Another way using CTE.
另一种使用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
Note :We cannot use JOINinside CTEwhen you want to delete.
注意：我们不能在你想要的时候使用JOIN里面。CTEdelete
