Search code examples
sqloraclejoinsql-delete

On delete Error: SQL Error: ORA-00933: SQL command not properly ended

I'm trying to delete a student record from the database using the below query. But I face this error.

DELETE student, enrolment FROM student INNER JOIN enrolment
WHERE student.stu_nbr=enrolment.stu_nbr and student.stu_nbr = 154;

Error report - SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended"

Solution

  • Give this a shot to delete student record for which there exists an enrollment.

    delete from student s
where stu_nbr = 154
  and exists (
    select 1
    from enrollment
    where stu_nbr = s.stu_nbr
  );

    If you are interested in deleting a student record from both student table and enrollment table, run 2 queries:

    delete from enrollment where stu_nbr = 154;
delete from student where stu_nbr = 154;

    If you want to automatically delete enrollment record when you delete a student, you may want to look into triggers.