TSQL Delete Using Inner Joins

Question

The following SQL statement executes fine on my database:

SELECT * FROM tblKPIs AS k 
INNER JOIN tblKeyPointLinks AS l ON k.KPIID = l.KPIID 
INNER JOIN tblKeyPoints AS p ON p.KptID = l.KptID 
INNER JOIN tblHistory AS h ON h.HistoryID = p.HistoryID 
WHERE h.CaseNo = 50043;

Yet the equivalent Delete statement gives an error 'syntax near AS'?

DELETE FROM tblKPIs AS k 
INNER JOIN tblKeyPointLinks AS l ON k.KPIID = l.KPIID 
INNER JOIN tblKeyPoints AS p ON p.KptID = l.KptID 
INNER JOIN tblHistory AS h ON h.HistoryID = p.HistoryID 
WHERE h.CaseNo = 50043;

Can I not use Joins in Delete statements?

If not how do I perform the above Delete?

EDIT

The table tblKeyPointLinks is an intermediate table to establish a many-to-many relationship between tblKPIs and tblKeyPoints. Therefore the SELECT statement returns some of the entries in tblKPIs more than once. Is this why the DELETE statement is having problem perhaps? What is the best way to work around this?

Answer 1

Yes you can JOIN in delete statements:

DELETE k FROM tblKPIs AS k 
INNER JOIN tblKeyPointLinks AS l ON k.KPIID = l.KPIID 
INNER JOIN tblKeyPoints AS p ON p.KptID = l.KptID 
INNER JOIN tblHistory AS h ON h.HistoryID = p.HistoryID 
WHERE h.CaseNo = 50043;

Answer 2

You could just do:

DELETE FROM tblKPIs 
  WHERE id in (
    SELECT id 
      FROM tblKPIs AS k 
      INNER JOIN tblKeyPointLinks AS l ON k.KPIID = l.KPIID 
      INNER JOIN tblKeyPoints AS p ON p.KptID = l.KptID 
      INNER JOIN tblHistory AS h ON h.HistoryID = p.HistoryID 
      WHERE h.CaseNo = 50043)

Answer 3

The successful code was as follows:

DELETE k
FROM tblKPIs k  
     INNER JOIN tblKeyPointLinks l ON k.KPIID = l.KPIID
     INNER JOIN tblKeyPoints p ON p.KptID = l.KptID
     INNER JOIN tblHistory h ON h.HistoryID = p.HistoryID
WHERE h.CaseNo = 50043; 

Apparently DELETE statements don't like the use of the keyword AS By simply omitting the AS keyword the statement works fine.