CODEWITHSUNDEEP
mysqlphpmyadmin
senjougahara
senjougahara

Reputation: 287

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

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"

Upvotes: 2

Views: 1758

Answers (3)

user2864740
user2864740

Reputation: 61975

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.

Upvotes: 1

Jacob Cohen
Jacob Cohen

Reputation: 1272

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

Upvotes: 0

echo_Me
echo_Me

Reputation: 37253

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 )

Upvotes: 0

Related Questions