MySQL joins for delete

Question

I have multiple tables list_of_students, sport_students.

in sport_students I have field named

list_of_house_id and in list_of_students I have field list_of_class_id, and list_of_house_id.

Now but I need to delete records from sport_student acc to house_id and list_of_class_id.

I have tried this query but seems not working

DELETE FROM `sport_students` JOIN list_of_students as student on `sport_student.list_house_id`= `student.list_of_house_id` WHERE `sport_student.list_of_house_id`=1 and `list_of_students.list_of_class_id`=1

Anybody have idea how to do this?

Similar to this http://stackoverflow.com/questions/652770/mysql-delete-w-join — Shyam K, Dec 17 '12 at 06:54

score 0 · Answer 1 · answered Dec 17 '12 at 06:41

0

Try this:

DELETE sp
  FROM `sport_students` sp
  JOIN list_of_students as student 
    ON sp.list_house_id = student.list_of_house_id
 WHERE sp.list_of_house_id=1 
   AND student.list_of_class_id=1;

For more see MySQL: Delete Syntax

answered Dec 17 '12 at 06:41

Himanshu

31,810
31
111
133

score 0 · Accepted Answer · edited Dec 17 '12 at 11:45

0

DELETE  FROM sport_students
WHERE   list_of_house_id IN (
            SELECT  list_of_house_id
            FROM    list_of_students
            WHERE   list_of_class_id = 1 AND list_of_house_id =1 
        )

edited Dec 17 '12 at 11:45

Himanshu

31,810
31
111
133

answered Dec 17 '12 at 07:05

Hitu Bansal

2,917
10
52
87

MySQL joins for delete

2 Answers2