Deleting a row from joined tables using group by and having clauses

Question

I want to delete a row from both table (enrol, course) if there are less than 3 enrolees of that course.

DELETE enrol, course
FROM enrol
INNER JOIN course
ON enrol.course_id = course.id
WHERE enrol.course_id in (SELECT enrol.course_id group by enrol.course_id having count(*)<3)

Instead of deleting the course with less than 3 enrolees, it deletes ALL of my data. I don't know what is wrong with this, please help.

Table 'course':

Table 'enrol':

SELECT enrol.course_id, course.id
FROM enrol
INNER JOIN course
ON enrol.course_id = course.id
group by enrol.course_id having count(*)<3

output:

Desired Output: All rows within enrol.course_id and course.id with value the same with above output should be deleted.

Answer 1

The problem with your code is the subquery:

SELECT enrol.course_id group by enrol.course_id having count(*)<3

which, although is missing a FROM clause, it runs without a syntax error in MySql, but produces unexpected results.

Join the correct version of that subquery to the 2 tables like this:

DELETE c, e
FROM course c
LEFT JOIN enrol e ON e.course_id = c.id
LEFT JOIN (SELECT course_id, COUNT(*) count FROM enrol GROUP BY course_id) t
ON t.course_id = c.id
WHERE e.course_id IS NULL OR t.count < 3;

I use LEFT joins so that even courses with no enrolees will be deleted.

See a simplified demo.