-4
DELETE f.* FROM FILE f, topic t, course c WHERE f.topic_id = t.topic_id AND t.course_id = 28;

DELETE t.* FROM topic t WHERE t.course_id = 28;

DELETE c.* FROM course c WHERE c.course_id = 28;

EDIT: The op wants to know (as indicated in the comments that clarified the question) that he would like to delete rows in all three tables where they share an id... essentially a cascading delete across three tables.

Mark Giaconia
  • 3,844
  • 5
  • 20
  • 42
user3653924
  • 1
  • 2
  • 3
    Explain your problem please. – Viktor Svensson May 19 '14 at 21:15
  • 3
    So, what's the question here? didn't get that. – Rahul May 19 '14 at 21:15
  • Welcome to SO! if you have constraints set up between the tables, you should be able to do a cascading delete. check this out. http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete – Mark Giaconia May 19 '14 at 21:17
  • You want to delete the reference across all tables right? – Mark Giaconia May 19 '14 at 21:20
  • i want delete the course from table and at that time i want delete the course topics and topic files delete as well as – user3653924 May 19 '14 at 21:27
  • #markg yes i want to delete the reference across all tables – user3653924 May 19 '14 at 21:32
  • use @ instead of # to reference a user :)... OK so if you can define foreign keys a certain way then you can let mysql do cascading deletes. Otherwise you will have to handle the three deletes in a transaction like a stored proc or something. Look at this: http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html – Mark Giaconia May 19 '14 at 21:34

1 Answers1

1
DELETE f.*, t.*, c.* FROM file f
JOIN topic t ON t.topic_id = f.topic_id
JOIN course c ON c.course_id = f.topic_id
WHERE f.topic_id = 28

This corresponds to the Multiple-table syntax in the MySQL documentation. It says:

For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions.

The other way you can do this is to declare the columns as foreign keys to the parent table (I'm not sure what the parent/child relation is in your tables), and use the ON DELETE CASCADE option to the foreign keys. Then you can just delete the row from the parent table, and the related rows will be deleted automatically from the child tables. Note that foreign key enforcement requires that you use InnoDB, not MyISAM, as the database engine.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • i want do some thing like that bt not woking... DELETE f.*, t.*, c.* FROM course c, topic t, FILE f WHERE c.course_id = 26 AND (t.course_id =c.course_id AND (f.topic_id =t.topic_id)) – user3653924 May 19 '14 at 21:28
  • I think that should work. Please make a sqlfiddle demonstrating the problem. – Barmar May 19 '14 at 21:33
  • listen i have 3 tables in database course topic and file i want delete course from php than how to delete complete course from table where topic have FK course id and files have FK topic id. – user3653924 May 19 '14 at 21:46
  • I understand what you have. I think my updated query should do what you want. – Barmar May 19 '14 at 21:47
  • I want do something like this bt its not working... :( DELETE f. *, t. *, c. * FROM course c INNER JOIN topic t ON c.course_id = t.course_id INNER JOIN FILE f ON f.topic_id = t.topic_id WHERE c.course_id = 26 – user3653924 May 19 '14 at 21:53
  • I asked you to create a sqlfiddle so I can try to debug this. If you don't do that, I'm done with this question. – Barmar May 19 '14 at 21:54