mySQL DELETE from one table using field from natural joining with another table?

Question

DELETE FROM `takes` WHERE ID, course_ID IN
(SELECT ID, course_ID FROM `takes` natural join `student`
where name = 'Jane' and course_ID='BIOL101')

From the takes table, I am trying to drop all Janes who are enrolled in BIOL101.

The SELECT gives me the correct set of IDs and course_IDs, and I am just trying to DELETE that from the takes table. Having trouble figuring out the proper syntax.

phpMyAdmin gives me the error: "#1093 - You can't specify target table 'takes' for update in FROM clause"

Anyway, see http://stackoverflow.com/a/4192849/2864740 , http://stackoverflow.com/a/2763245/2864740 , and http://dev.mysql.com/doc/refman/5.7/en/delete.html for "DELETE .. JOIN" syntax - boo to using IN for join logic! — user2864740, May 24 '14 at 07:49

score 1 · Accepted Answer · edited May 23 '17 at 12:20

1

Consider not [ab]using IN, but rather use a proper DELETE..JOIN which MySQL does support.

DELETE takes
FROM takes
JOIN student s
  -- The relation/schema is unclear; adapt as required
  -- but MAKE SURE A RELATION IS ESTABLISHED!
  ON s.student_id = takes.student_id
WHERE s.name = 'Jane'
  AND takes.course_ID = 'BIOL101'

Also see this answer.

edited May 23 '17 at 12:20

Community

1
1

answered May 24 '14 at 07:56

user2864740

60,010
15
145
220

score 0 · Answer 2 · answered May 24 '14 at 07:48

0

Try this

  DELETE FROM `takes` WHERE (ID, course_ID) IN
  (select * FROM(
 (SELECT ID, course_ID FROM `takes` natural join `student`
  where name = 'Jane' and course_ID='BIOL101')t )

answered May 24 '14 at 07:48

echo_Me

37,078
5
58
78

score 0 · Answer 3 · answered May 24 '14 at 07:49

0

DELETE
FROM `takes` 
WHERE `ID` IN 
(
    SELECT ID FROM `takes`
    NATURAL JOIN `student`
    WHERE `name` = 'Jane` AND `course_ID` = 'BOIL101'
)

answered May 24 '14 at 07:49

Jacob Cohen

1,232
3
13
33

mySQL DELETE from one table using field from natural joining with another table?

3 Answers3