Using DELETE and joins in stored procedures

Question

I'm trying to delete any record where the FeeDueDate is greater than 3 weeks but Im not sure how to go about implementing this. The problem seems to be with the join syntax.

    CREATE PROC spAreFeesOverDue 
    AS
    Delete 
    FROM Assignment.dbo.PupilDetails PDetails
    join Assignment.dbo.TuitionFees Fees
    ON PDetails.Pupil_ID = Fees.Pupil_ID
    WHERE FeeDueDate < dateadd(WEEK, -3, getdate());

Possible duplicate of [How to Delete using INNER JOIN with SQL Server?](http://stackoverflow.com/questions/16481379/how-to-delete-using-inner-join-with-sql-server) — Igor, Mar 25 '17 at 11:04

score 0 · Answer 1 · answered Mar 25 '17 at 11:28

You need to specify the table you are deleting from. For instance:

DELETE Fees
    FROM Assignment.dbo.PupilDetails PDetails JOIN
         Assignment.dbo.TuitionFees Fees
         ON PDetails.Pupil_ID = Fees.Pupil_ID
    WHERE FeeDueDate < dateadd(WEEK, -3, getdate());

score 0 · Accepted Answer · answered Mar 25 '17 at 11:30

Please try this query:-

CREATE PROC spAreFeesOverDue 
   AS
  BEGIN 
       DELETE  
       FROM Assignment.dbo.PupilDetails PDetails
       join Assignment.dbo.TuitionFees Fees
       ON PDetails.Pupil_ID = Fees.Pupil_ID
      WHERE FeeDueDate < dateadd(WEEK, -3, getdate())
   END;

Using DELETE and joins in stored procedures

2 Answers2