SQl Delete from more than one table

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

alter table tablename drop column columnname