Delete from table on join

Question

I am trying to remove all records from table based on the date of another for example I have one table called pd_course_mstr that has the following fields

course_id
start_dt

Then I have another table called pd_eval_dtl that has the following fields in it

course_id
eval_question

The goal is to delete all eval questions that have a particular date. I was able to use SQL to select all the eval questions by using the following statement

SELECT * 
FROM pd_eval_dtl AS eval JOIN pd_course_mstr AS course 
    ON eval.course_id = course.course_id 
WHERE course.start_dt='02/17/2014'

So I tried to change it to

DELETE 
FROM pd_eval_dtl JOIN pd_course_mstr 
    ON pd_eval_dtl.course_id = pd_course_mstr.course_id 
WHERE pd_course_mstr.start_dt='02/17/2014'

but it keeps saying I have a syntax error near JOIN

I don't know what I am doing wrong.

Answer 1

The syntax is

DELETE FROM eval
FROM   pd_eval_dtl AS eval
       INNER JOIN pd_course_mstr AS course
         ON eval.course_id = course.course_id
WHERE  ( course.start_dt = '20140217' ) 

Answer 2

DELETE FROM pd_eval_dtl
WHERE EXISTS 
             (SELECT 1 
              FROM pd_course_mstr
              WHERE course_id  = pd_eval_dtl.course_id
              AND start_dt='02/17/2014')

Answer 3

Try this.

DELETE ped
FROM pd_eval_dtl ped 
    INNER JOIN pd_course_mstr pcm ON ped.course_id = pcm.course_id 
WHERE pcm.start_dt='20140217'

Answer 4

Use a where in:

delete pd_eval_dtl
where  course_id in
       ( select course_id
         from   pd_course_mstr
         where  start_dt='02/17/2014'
       )

Answer 5

 DELETE FROM SECONDTABLE WHERE COURSE_iD IN (SELECT COURSE_iD FROM FIRST TABLE WHERE START_dT IS > REQUIRED DATE)