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

Question

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"

See this: https://stackoverflow.com/questions/12672082. It shows how to delete data based on joins in oracle. Here's another example: https://dba.stackexchange.com/questions/134622 — zedfoxus, May 18 '20 at 06:00
You can't delete from multiple tables with the DELETE statement. — , May 18 '20 at 06:02

score 2 · Accepted Answer · answered May 18 '20 at 06:02

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.

answered May 18 '20 at 06:02

zedfoxus

ORA-01407: cannot update ("USER"."ENROLMENT"."STU_NBR") to NULL I get this error, – Emma May 18 '20 at 06:42
Do you have a trigger on either tables or foreign key constraint? – zedfoxus May 18 '20 at 06:44
@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. – zedfoxus May 18 '20 at 17:49
@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. – zedfoxus May 22 '20 at 17:11

1 Answers1