0

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"

Improve this question
2

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

ORA-01407: cannot update ("USER"."ENROLMENT"."STU_NBR") to NULL I get this error,
Do you have a trigger on either tables or foreign key constraint?
@Emma you have asked a few questions on StackOverflow but you haven't marked any of your questions as answered despite receiving answers from the community. My recommendation is to go back to all questions you have answered and mark an answer as accepted that helped you the most. That'll give closure to your questions.
@Emma do you need any further help on this question? If this answer has solved the issue, you can put closure to your question by marking it as accepted, or you can wait for additional answers to appear and then choose to mark one as accepted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.