如何使用INNER JOIN与SQL服务器删除？

我想在SQLServer 2008中使用INNER JOIN。

但我得到了这个错误：

Msg 156， Level 15， State 1， Line 15
关键词'INNER'附近的语法不正确。

我的代码：

DELETEFROM WorkRecord2INNER JOIN EmployeeON EmployeeRun=EmployeeNoWHERE Company = '1'AND Date = '2013-05-06'
1946492 次浏览
小开
最佳答案

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

DELETE wFROM WorkRecord2 wINNER JOIN Employee eON EmployeeRun=EmployeeNoWHERE Company = '1' AND Date = '2013-05-06'
小开

只需在要删除记录的DELETE和FROM之间添加表的名称，因为我们必须指定要删除的表。还要删除ORDER BY子句，因为在删除记录时没有什么要订购的。

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

    DELETE WorkRecord2FROM WorkRecord2INNER JOIN EmployeeON EmployeeRun=EmployeeNoWHERE Company = '1'AND Date = '2013-05-06';
小开

这可能对你有帮助-

DELETE FROM dbo.WorkRecord2WHERE EmployeeRun IN (SELECT e.EmployeeNoFROM dbo.Employee eWHERE ...)

或者试试这个-

DELETE FROM dbo.WorkRecord2WHERE EXISTS(SELECT 1FROM dbo.Employee eWHERE EmployeeRun = e.EmployeeNoAND ....)
小开

试试这个：

DELETE FROM WorkRecord2FROM EmployeeWhere EmployeeRun=EmployeeNoAnd Company = '1'AND Date = '2013-05-06'
小开

试试这个查询：

DELETE WorkRecord2, EmployeeFROM WorkRecord2INNER JOIN Employee ON (tbl_name.EmployeeRun=tbl_name.EmployeeNo)WHERE tbl_name.Company = '1'AND tbl_name.Date = '2013-05-06';
小开

它应该是：

DELETE zpostFROM zpostINNER JOIN zcomment ON (zpost.zpostid = zcomment.zpostid)WHERE zcomment.icomment = "first"
小开

在SQL服务器管理工作室中，我可以轻松创建SELECT查询：

SELECT Contact.Naam_Contactpersoon, Bedrijf.BedrijfsNaam, Bedrijf.Adres, Bedrijf.PostcodeFROM ContactINNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

我可以执行它，并显示我所有的联系人。

现在将SELECT更改为DELETE：

DELETE ContactFROM ContactINNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijf

您在SELECT语句中看到的所有记录都将被删除。

您甚至可以使用相同的过程创建更困难的内连接，例如：

DELETE FROM ContactINNER JOIN Bedrijf ON Bedrijf.IDBedrijf = Contact.IDbedrijfINNER JOIN LoginBedrijf ON Bedrijf.IDLoginBedrijf = LoginBedrijf.IDLoginBedrijf
小开

这是我的SQL服务器版本

DECLARE @ProfileId table(Id bigint)
DELETE FROM AspNetUsersOUTPUT deleted.ProfileId INTO @ProfileIdWHERE Email = @email
DELETE FROM UserProfilesWHERE Id = (Select Id FROM @ProfileId)
小开

您没有为Company和Date指定表，您可能想要修复它。

使用MERGE的标准SQL：

MERGE WorkRecord2 TUSING Employee SON T.EmployeeRun = S.EmployeeNoAND Company = '1'AND Date = '2013-05-06'WHEN MATCHED THEN DELETE;

来自Devart的答案也是标准SQL，尽管不完整。它应该看起来更像这样：

DELETEFROM WorkRecord2WHERE EXISTS ( SELECT *FROM Employee SWHERE S.EmployeeNo = WorkRecord2.EmployeeRunAND Company = '1'AND Date = '2013-05-06' );

关于上述内容需要注意的重要一点是，很明显，删除是针对单个表的，正如在第二个示例中通过要求标量子查询来强制执行的那样。

对我来说，各种专有的语法答案更难阅读和理解。我想这种心态在答案来自Frans Eilering中得到了最好的描述，即编写代码的人不一定关心将阅读和维护代码的人。

小开

另一种方法是使用CTE：

;WITH cteAS (SELECT *FROM   workrecord2 wWHERE  EXISTS (SELECT 1FROM   employee eWHERE  employeerun = employeenoAND company = '1'AND date = '2013-05-06'))DELETE FROM cte

备注：当您想要delete时，我们不能在CTE中使用JOIN。

小开

以下是我目前用于删除甚至更新的内容：

DELETE           wFROM             WorkRecord2   w,Employee      eWHERE            w.EmployeeRun = e.EmployeeNoAND w.Company = '1'AND w.Date = '2013-05-06'
小开

这是一个简单的查询，一次从两个表中删除记录。

DELETE table1.* ,table2.*FROM table1INNER JOIN table2 ON table1.id= table2.id where table1.id ='given_id'
小开

你甚至可以执行子查询。像下面的代码：

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

使用事务块、表变量和JOIN删除多个表数据。

BEGIN TRANSACTION;
declare @deletedIds table ( id int );   
DELETE woutput deleted.EmployeeRun into @deletedIdsFROM WorkRecord2 wINNER JOIN Employee eON e.EmployeeNo = w.EmployeeRunAND w.Company = 1AND w.date = '2013-05-06';
DELETE eFROM Employee as eINNER JOIN @deletedIds as dON d.id = e.EmployeeNo;COMMIT TRANSACTION;

请从urlhttps://dbfiddle.uk/?rdbms=sqlserver_2014&；fiddle=43330dda6f1b71b8ec4172a24d5b6921检查

使用临时表和JOIN删除多个表数据。删除后删除临时表。

BEGIN TRANSACTION;
-- create temporary tablecreate table #deletedRecords (employeeId int);    
-- INSERT INTO #deletedRecordsSELECT e.EmployeeNoFROM WorkRecord2 wINNER JOIN Employee eON e.EmployeeNo = w.EmployeeRunAND w.Company = 1AND w.date = '2013-05-06';          
-- delete from WorkRecord2DELETE wFROM WorkRecord2 wINNER JOIN #deletedRecords dON w.EmployeeRun = d.employeeId;           
-- delete from Employee using existsDELETEFROM EmployeeWHERE EXISTS (SELECT 1FROM #deletedRecords dWHERE d.employeeId = EmployeeNo);                  
-- drop temporary tableDROP TABLE #deletedRecords;
COMMIT TRANSACTION;

请从urlhttps://dbfiddle.uk/?rdbms=sqlserver_2014&； fiddle=d52c6c1ed91669d68fcc6bc91cb32d78检查

使用SELECT INTO创建临时表的替代方法

BEGIN TRANSACTION;
SELECT  e.EmployeeNo employeeIdINTO #deletedRecordsFROM WorkRecord2 wINNER JOIN Employee eON e.EmployeeNo = w.EmployeeRunAND w.Company = 1AND w.date = '2013-05-06';          
-- delete from WorkRecord2DELETE wFROM WorkRecord2 wINNER JOIN #deletedRecords dON w.EmployeeRun = d.employeeId;           
-- delete from Employee using existsDELETEFROM EmployeeWHERE EXISTS (SELECT 1FROM #deletedRecords dWHERE d.employeeId = EmployeeNo);                  
-- drop temporary tableDROP TABLE #deletedRecords;
COMMIT TRANSACTION;

请从urlhttps://dbfiddle.uk/?rdbms=sqlserver_2014&； fiddle=0f02f05616ce5b4dcc8fc67c6cf1e640检查

使用JOIN删除单个表数据

DELETE wFROM WorkRecord2 wINNER JOIN Employee eON e.EmployeeNo = w.EmployeeRunAND w.Company = 1AND w.date = '2013-05-06'

请从urlhttps://dbfiddle.uk/?rdbms=sqlserver_2014&；小提琴=84a60d1368556a8837281df36579334a检查

使用CTE删除单表数据

WITH cte AS (SELECT w.EmployeeRunFROM WorkRecord2 wWHERE EXISTS (SELECT 1FROM EmployeeWHERE EmployeeNo = w.EmployeeRun)AND w.Company = 1AND w.date = '2013-05-06')DELETEFROM cte

请从urlhttps://dbfiddle.uk/?rdbms=sqlserver_2014&；fiddle=6590007b3c8c2ffad5563bd86606c5b1检查

在创建子表时使用关于级联删除外键。如果删除父表数据，则会自动删除相应的子表数据。


提交答案