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'.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With