mySQL delete based on two tables

Question

In mySQL I have two tables (course and section) and I want to delete all sections that are 1 credit and on Mondays. The course table has the credits for the course and the section table has the days for that section of the course. Here's what I have for my query but I get errors:

DELETE FROM
    section
WHERE
    course_ID
IN (
    SELECT course_ID
    FROM course
    NATURAL JOIN section
    WHERE credits = '1'
    AND days LIKE '%M%'
);

Can anyone help with this?

Answer 1

could you have a same table exception for table section

for avoid this so you could try using a join on a subquery instead of IN clause

DELETE s 
FROM section s
INNER JOIN (
    SELECT course_ID
    FROM course
    NATURAL JOIN section
    WHERE credits = '1'
    AND days LIKE '%M%'

) t  on t.course_ID = s.course_ID

Answer 2

YOu probably don't need to reopen section in the subquery. I suspect that you could phrase the query as:

delete s
from section s
natural join course c
where c.credit = 1 and c.days like '%M%'

I am not a big fan of natural joins, because it is not explicit about the joining logic (and depends on matching column names rather than actual schema dependencies, such as foreign keys). I would recommend switching to a regular join, maybe using the using() clause.

If there may be multiple rows in course that match on a a given section, then exists is more appropriate than a join. Assuming that column section_id can be used to relate the two tables, you would do:

delete s
from section s
where exists (
    select 1
    from course c
    where c.credit = 1 and c.days like '%M%' and c.section_id = s.section_id
)