deleting records from multiple tables

[ferhanz]

i have got 3 tables

1) student:
student_id
student_name
student_address

2)subjects
subject_id
student_id
subject_name

3)hobbies
hobby_id
student_id
hobby_name

what i want is that when i delete the record from student table it should delete all the records of that id from subjects and hobbies table, i am using phpmyadmin for creating tables and the type of the table is default MyISAM

Thanks

Farhan Nawazish

[Birdman]

I'm pretty sure you must delete from each table separately.

delete from student where student id = 100
delete from subjects where student id = 100
delete from hobbies where student id = 100

BTW, welcome to Webmaster World!

[Salsa]

This should work with one query:

DELETE student, subjects, hobbies FROM student, subjects, hobbies WHERE student.student_id = subjects.student_id OR student.student_id = hobbies.student_id

This isn't tested just now, but I think it should work.

[Salsa]

Oops, I think you're going to need one more WHERE condition, or you're going to delete all records. Try like:

DELETE student, subjects, hobbies FROM student, subjects, hobbies WHERE [b]student.student_ID = 100 AND ([/b]student.student_id = subjects.student_id OR student.student_id = hobbies.student_id[b])[/b]

Backup first!

[ferhanz]

thanks Salsa :D it worked

[ferhanz]

well i took the backup and write that ID thing myself ;)

thanks for quick response

[Salsa]

Great. I'm glad it worked for you.

You must have already learned the lesson to always backup before messing with more complex queries like this ;)

... and welcome, again, to webmasterworld!

[pete_m]

I've got a tip when you're creating complex DELETE queries like this one.

Always create them as SELECT queries whilst you're writing them. This way, when you run your query you'll see exactly which rows will be deleted. Once you're happy with the results just change the SELECT to DELETE.

[coopster]

Related discussion from a few months back...

Deleting Rows from MySQL [webmasterworld.com]