Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Deleting rows by multiple joins

I am trying to delete the rows by joining the multiple tables as you can see but it throws me an error:

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

getting error:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'INNER'.

like image 917
Nick Kahn Avatar asked Dec 16 '22 05:12

Nick Kahn


1 Answers

The correct syntax is

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

Applied to your example this would become

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

Note the two references to the Reg table

This can be shortened further by aliasing (although there's little need in this case)

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 Example

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;
like image 127
Lieven Keersmaekers Avatar answered Jan 12 '23 16:01

Lieven Keersmaekers