CODEWITHSUNDEEP
sqloracle-databasesql-deleteinsert-into
Jatin Gupta
Jatin Gupta

Reputation: 25

Delete from table inner join - ORA-00933: SQL command not properly ended

Aim is to insert records into a table only if same records donot exists in same table. So, I stored values of that table in a Cursor like this: -

cursor note_data is
        select  note_s.nextval, i_user_book_id, i_course_user_id, book_edition_id, book_id, n.role_type_id, page_id, book_page_number, 
                        xcoord, ycoord, width, height, share_across_courses, sysdate, i_user_id, description, share_with_students,text
        from    note n, course_user cu
        where   n.course_user_id = cu.course_user_id
        and     cu.course_id = i_from_course_id;

Now, I will delete the records from the table using inner join like this -

delete n 
from note n 
inner join course_user cu 
on n.course_user_id = cu.course_user_id 
where cu.course_id = i_from_course_id;

While executing this delete statement, I am getting error - 'ORA-00933: SQL command not properly ended'

If I will be able to delete the records from table, I will insert same records from cursor data into the same table using this code :-

FOR notecopy IN note_data LOOP
    insert into note (note_id, user_book_id, course_user_id, book_edition_id, book_id, role_type_id, page_id, book_page_number, 
                            xcoord, ycoord, width, height, share_across_courses, date_created, date_updated, created_by, updated_by, description, share_with_students,text)
    values (note_s.nextval, notecopy.i_user_book_id, notecopy.i_course_user_id, notecopy.book_edition_id, notecopy.book_id, notecopy.role_type_id, notecopy.page_id, notecopy.book_page_number, 
                            notecopy.xcoord, notecopy.ycoord, notecopy.width, notecopy.height, notecopy.share_across_courses, sysdate, sysdate, notecopy.i_user_id, notecopy.i_user_id, notecopy.description, notecopy.share_with_students, notecopy.text);
END LOOP;

My target is to insert records into a table only if same records donot exists in same table.

Please guide how to deal with the delete statement error as well as suggest am I following correct approach to achieve the target. If not, Please suggest some alternative way as well.

Thanks

Upvotes: 0

Views: 4051

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50067

You need to phrase this as a DELETE FROM (subquery)..., as in

DELETE
  FROM (SELECT *
          FROM NOTE n
          INNER JOIN COURSE_USER cu
            ON n.COURSE_USER_ID = cu.COURSE_USER_ID
          WHERE cu.COURSE_ID = i_from_course_id)

Not knowing anything about your tables, their columns, or their relationships I can't say if that will work as you're expecting - but that's the syntax used with Oracle.

That having been said, the advice to use MERGE to update-or-insert instead of deleting all the data and then re-inserting it may be valid.

Best of luck.

Upvotes: 0

JT4U
JT4U

Reputation: 620

delete from note n 
inner join course_user cu 
on n.course_user_id = cu.course_user_id 
where cu.course_id = i_from_course_id;

it should be delete from note n.

Use Merge looking at the link here. If you have questions on how to do them i will answer. https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Upvotes: 0

Related Questions