UPDATE record if present; else INSERT in Oracle

Question

Is there any Oracle function that indicate the existence of a record, or any other useful technique to achieve this?

EDIT: using the MERGE statement I did:

MERGE
 INTO  lims_min.mytab src
 USING lims_min.mytab tgt
    ON (    src.col1 = tgt.col1
        AND tgt.col1 = p_val1
        AND src.col2 = tgt.col2
        AND tgt.col2 = p_val2
       )

 WHEN MATCHED
 THEN
 UPDATE
  SET tgt.col3=p_val3,
      tgt.col4=p_val4

 WHEN NOT MATCHED
 THEN
 INSERT  (col1, col2, col3, col4)
 VALUES  (val1, val2, val2, val4);

I get the error saying that col3 is invalid identifier. No typos, and it is existing column.p_val1, p_val2, p_val3 and p_val4 are string parameters that are passed to the stored procedure. i am thinking that the issue may lie in these params, perhaps they should be placed inside a WHERE statement? Any ideas?

Oracle 10, I got "PL/SQL: ORA-00904: "P"."COL3": invalid identifier" — sarsnake, Jan 22 '11 at 00:15
i think you need to: "MERGE INTO tgt" (not src) - switch those around; and I don't think you need to join your src & tgt tables - just specify: ON (src.col1 = p_val1 and src.col2 = p_val2) — Gerrat, Jan 22 '11 at 00:33
Possible duplicate of [Oracle: how to UPSERT (update or insert into a table?)](http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) — John Y, Sep 30 '16 at 16:14

score 4 · Accepted Answer · answered Jan 21 '11 at 23:40

4

You are looking for merge in Oracle.

MERGE
       INTO  target_table tgt
      USING source_table src
         ON  ( src.object_id = tgt.object_id ) //The key to check if the record exists
       WHEN MATCHED // if exists
       THEN
     UPDATE
        SET   tgt.object_name = src.object_name //update it
        ,     tgt.object_type = src.object_type
       WHEN NOT MATCHED                         // if not exists
       THEN
     INSERT ( tgt.object_id                    //then insert
            , tgt.object_name
            , tgt.object_type )
     VALUES ( src.object_id
            , src.object_name
            , src.object_type );

answered Jan 21 '11 at 23:40

jhurtado

8,587
1
23
34

Can source and target be the same table? I am trying to do just that and I get a Oracle error that one of the columns (existing ones) is an invalid identifier.... – sarsnake Jan 21 '11 at 23:58
and no I don't update columns I do match on:) I try to update other columns, which is the whole point of this... – sarsnake Jan 22 '11 at 00:01
If properly aliased, you should be able to use the same source and target. – DCookie Jan 22 '11 at 00:02
1

The syntax above doesn't work in Oracle 10. As in doesn't compile. see edited question for code. Thanks. – sarsnake Jan 22 '11 at 00:05
Sorry, i was in the road, the above syntax is from 9i but shouldn't be much change for 10g, also note that you can use a virtual table for the source table, glad it helped. :) – jhurtado Jan 22 '11 at 07:17

score 4 · Answer 2 · answered Jan 21 '11 at 23:41

4

Look into the MERGE statement

http://psoug.org/reference/merge.html

answered Jan 21 '11 at 23:41

Guy

9,720
7
38
42

UPDATE record if present; else INSERT in Oracle

2 Answers2