通过多个连接删除行

Question

我正在尝试通过连接多个表来删除行，如您所见，但它给我抛出了一个错误：

DELETE FROM Reg INNER JOIN
            RegDoc ON Reg.RegId = RegDoc.RegId INNER JOIN
            Doc ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

出现错误：

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

说明：这是一个与 INNER 关键字相关的语法错误。

- Nick Kahn

Answer 1

正确的语法是

DELETE [ FROM ] { <object> } 
[ FROM <table_source>]
[ WHERE { <search_condition> } ]

应用到您的示例中，这将变成：

DELETE FROM Reg 
FROM  Reg
      INNER JOIN RegDoc ON Reg.RegId = RegDoc.RegId 
      INNER JOIN Doc ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

注意到对Reg表的两个引用

这可以通过别名进一步缩短（虽然在这种情况下几乎没有必要）

DELETE FROM r
FROM  Reg r
      INNER JOIN RegDoc ON r.RegId = RegDoc.RegId 
      INNER JOIN Doc ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

---

MSDN 示例

DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;

- Lieven Keersmaekers

---

应该是 DELETE reg FROM ...，对吧？ - Farray

@Farray - DELETE FROM 是正确的，但第一个 FROM 可以省略。这有点像写出 LEFT OUTER JOIN，其中 OUTER 也是多余的，但我仍然倾向于完全写出它。 - Lieven Keersmaekers

delete from t1 from Table1 as t1 中的第一个 from 是可选的，但是即使不加也能正常工作。 - Andomar

Answer 2

请使用以下内容

DELETE 
FROM Reg 
FROM Reg 
INNER JOIN
            RegDoc ON Reg.RegId = RegDoc.RegId INNER JOIN
            Doc ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

更清晰地说，我认为：

DELETE Reg 
FROM Reg 
INNER JOIN RegDoc 
  ON Reg.RegId = RegDoc.RegId 
INNER JOIN Doc 
  ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

- Oleg Dok

Answer 3

你可以这样做：

DELETE Reg
FROM Reg 
INNER JOIN RegDoc ON Reg.RegId = RegDoc.RegId 
INNER JOIN Doc ON RegDoc.DocId = Doc.DocId
WHERE Doc.Name LIKE N'%Title%'

- Jon Egerton

Answer 4

您可以使用别名，但最好使用与表名不同的别名，这样更清晰：

DELETE  r 
FROM    Reg as r
INNER JOIN RegDoc rd ON r.RegId = rd.RegId 
INNER JOIN Doc d ON rd.DocId = d.DocId

您实际上可以删除rd和删除d。

- Andomar

Answer 5

与其他答案略有不同的方式（这些答案都很好）

DELETE FROM Reg
WHERE Reg.RegID IN (SELECT RegDoc.RegId
                    FROM RegDoc
                      INNER JOIN Doc 
                        ON RegDoc.DocId = Doc.DocId
                    WHERE Doc.Name LIKE N'%Title%')

- Mikael Eriksson