Search code examples
sqlsubquerysql-delete

Delete from table using subquery from another table

Tables I need to delete from the students table all students who got less than the average percentage of correct answers on the exam_results table. It has to be done using subqueries.

So far I've tried:

DELETE FROM students
WHERE id IN (SELECT student_id FROM exam_results WHERE percentage < AVG(percentage));

This returns: ERROR 1111: Invalid use of group function

Why is this an invalid use of group function? What am I missing or is wrong with this?

Any help is much appreciated! Thank you!

Solution

  • You need to find the student in sub-query as follows:

    DELETE FROM students
 WHERE id IN 
   (SELECT student_id 
      FROM exam_results 
     WHERE percentage < (select AVG(percentage) from exam_results);

    You can also use the analytical function but that depends on your MySql Version. so please tag the database and its version on which you are working.