SQl Delete from more than one table

Say, I have two tables Courses and faculty_courses - each has a primary key course_ID that is varchar(50) not null.

I am trying to delete a row from the Courses table - so assume have to delete that row from both tables since they are linked by a relationship.

I wrote this - doesn't work - says Incorrect syntax near the keyword 'JOIN'

DELETE FROM Courses JOIN faculty_courses ON Courses.course_ID = faculty_courses.course_ID WHERE faculty_courses.course_ID = 'ITM731'

Any ideas?

From stackoverflow

• You have to issue two statements.

  DELETE Courses where course_ID = 'ITM731'
  DELETE faculty_courses WHERE course_ID = 'ITM731'
  

  Or, as mentioned here, use a delete cascade.

  JoshBerke : I typically recommend this instead of cascading deletes. This is a lot more visible as what the intention is.

  Tom H. : +1 on the comment Josh :)

• What you want can be handled by having ON DELETE CASCADE links between the tables. Provided that your RDBMS allows for this.

• You can create a FK reference with CASCADE Delete

  Edit

  I recommend that you use the approach from Ocedecio of explicitly deleting from the two tables as opposed to cascading. It makes your intention so much clearer.

• DELETE only delete record from one table - the JOIN syntax is useful only for selecting the correct rows on that table, for example, on table with an 1:n relationship (not what you have here) and you want to delete all the records from the "n" table that pertain to a selectable record on the "1" table, then you'd do DELETE FROM ntable INNER JOIN reftable ON ntable.ref = reftable.ref WHERE reftable.column = 'whatyouneedtolookup'.

  Now as I said, this does not pertain to your current situation, in which you simply need to issue two DELETE statements - one for each table, on the same key.

  That being said, it sounds like you have a 1:1 reference between the table which strikes me as odd - normally with that kind of reference, the trivial normalization would be to merge the tables into a single table with the columns from both tables.

  Tom H. : 1:1 relationships can be valid in the case of subtyping an entity. In this case though I think he probably misspoke about the PK being the one column in both tables. Maybe faculty_courses is a composite key including the course_id?

• alter table tablename drop column columnname