How to INSERT a row in a table if this row doesn't already exist in same table

Question

How to INSERT a row in a table if this row doesn't already exist in same table? I want something like this.

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)

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, sysdate, i_user_id, i_user_id, description, share_with_students,text

from  note n inner join course_user cu
on n.course_user_id = cu.course_user_id
where     cu.course_id = 23846
and where not exists (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, sysdate, i_user_id, i_user_id, description, share_with_students,text

from  note n inner join course_user cu
on n.course_user_id = cu.course_user_id
where     cu.course_id = 23846);

That is, in note table if record is already present for a particular course_user_id then do nothing. Otherwise if no entry for that particular course_user_id then insert into note for that course_user_id.

But my code is not working.

Here, note_id is PRIMARY KEY in note table and Course_user_id is PRIMARY KEY in course_user table.

you might want to look at the MERGE statement, this gives you options on what to do if the row already exists in the table — davegreen100, May 24 '16 at 08:17
Possible duplicate of [Oracle insert if not exists statement](http://stackoverflow.com/questions/10824764/oracle-insert-if-not-exists-statement) — MT0, May 24 '16 at 08:24

Avrajit Roy · Answer 1 · 2016-05-24T11:26:25.083

You can try MERGE to accomplish this. Hope below snippet helps. I have not tested it as i don't have workspace with me no\w.

MERGE INTO note nt USING
(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,
  sysdate,
  i_user_id,
  i_user_id,
  description,
  share_with_students,
  text
FROM note n
INNER JOIN course_user cu
ON n.course_user_id = cu.course_user_id
WHERE cu.course_id  = 23846
AND NOT EXISTS
  (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,
    sysdate,
    i_user_id,
    i_user_id,
    description,
    share_with_students,
    text
  FROM note n
  INNER JOIN course_user cu
  ON n.course_user_id     = cu.course_user_id
  ))A ON (a.course_user_id = nt.course_user_id) 
  WHEN MATCHED THEN
UPDATE SET nt.bookId = a.book_id /*-- dummy update*/
  WHEN NOT MATCHED THEN
INSERT
  (
    nt.note_id,
    nt.user_book_id,
    nt.course_user_id,
    nt.book_edition_id,
    nt.book_id,
    nt.role_type_id,
    nt.page_id,
    nt.book_page_number,
    nt.xcoord,
    nt.ycoord,
    nt.width,
    nt.height,
    nt.share_across_courses,
    nt.date_created,
    nt.date_updated,
    nt.created_by,
    nt.updated_by,
    nt.description,
    nt.share_with_students,
    nt.text
  )
  VALUES
  (
    a.note_id,
    a.user_book_id,
    a.course_user_id,
    a.book_edition_id,
    a.book_id,
    a.role_type_id,
    a.page_id,
    a.book_page_number,
    a.xcoord,
    a.ycoord,
    a.width,
    a.height,
    a.share_across_courses,
    a.date_created,
    a.date_updated,
    a.created_by,
    a.updated_by,
    a.description,
    a.share_with_students,
    a.text
  );

I used your code but it gives ERROR at line 7: ORA-00936: missing expression. Pls guide. — Jatin Gupta, May 24 '16 at 11:05

Avrajit Roy · Answer 2 · 2016-05-24T11:21:15.973

Moreover you can try another approach using ORACLE HINTS to avoid dup val on INDEX insertion as illustrated below. But this is applicable for 11g oracle DB and above. Hope this helps.

INSERT
  /*+ ignore_row_on_dupkey_index(note_id) */
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
  )
  (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,
      sysdate,
      i_user_id,
      i_user_id,
      description,
      share_with_students,
      text
    FROM note n
    INNER JOIN course_user cu
    ON n.course_user_id = cu.course_user_id
    WHERE cu.course_id  = 23846
    AND NOT EXISTS
      (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,
        sysdate,
        i_user_id,
        i_user_id,
        description,
        share_with_students,
        text
      FROM note n
      INNER JOIN course_user cu
      ON n.course_user_id = cu.course_user_id
      WHERE cu.course_id  = 23846
      ));

I recommend to add this answer in the duplicate question, this question will be closed. — Florin Ghita, May 24 '16 at 08:51
Errors for PACKAGE BODY EBOOK_VIEWER_PKG: LINE/COL ERROR 2239/12 PL/SQL: SQL Statement ignored 2283/16 PL/SQL: ORA-00936: missing expression 2383/6 PL/SQL: SQL Statement ignored 2427/16 PL/SQL: ORA-00936: missing expression — Jatin Gupta, May 24 '16 at 09:43
Could u plz try now. There were some syntactical rrors which I have rmoed\. Let me know if this helps. Also I have updated the MERGE answer too with no syntactical errors. — Avrajit Roy, May 24 '16 at 11:22

How to INSERT a row in a table if this row doesn't already exist in same table

2 Answers2