Oracle: on Duplicate Key Update

Oracle DB equivalent of on duplicate key update

You would need to use a MERGE. Something like

MERGE INTO users dest
  USING( SELECT 1 user_id, 10 points FROM dual) src
     ON( dest.user_id = src.user_id )
 WHEN MATCHED THEN
   UPDATE SET points = src.points
 WHEN NOT MATCHED THEN
   INSERT( user_id, points ) 
     VALUES( src.user_id, src.points );

ON DUPLICATE KEY UPDATE in oracle or equiv (defining key/column?)

ON DUPLICATE KEY is not a thing in Oracle. The closest equivalent is the MERGE syntax. Here is an example on how to use it for your use case (I reduced the number of columns for the sake of readability):

merge into app.also_data a
using (select ? app_id, ? fac_ident, ? lg_name, ? basic_tp from dual) p
on (a.app_id = p.app_id)
when matched then 
    update set 
        a.fac_ident = p.fac_ident, 
        a.lg_name = p.lg_name, 
        a.basic_tp = p.basic_tp
when not matched then 
    insert(app_id, fac_ident, lg_name, basic_tp) 
    values(p.app_id, p.fac_ident, p.lg_name, p.basic_tp)
    

SQL query with ON DUPLICATE KEY UPDATE clarification needed

Oracle doesn't support the ON DUPLICATE KEY UPDATE syntax. That appears to be MySQL-specific syntax.

Most likely, you would appear to want a MERGE statement

MERGE INTO table1 t1
  USING (SELECT col1, col2, col3 
           FROM table2) ss
     ON (t1.col1 = ss.col1) -- whatever the key is
   WHEN MATCHED THEN
     UPDATE SET t1.col1 = ss.col1,
                t1.col2 = ss.col2,
                t1.col3 = ss.col3
   WHEN NOT MATCHED THEN 
     INSERT( t1.col1, t1.col2, t1.col3 )
       VALUES( ss.col1, ss.col2, ss.col3 )

UPDATE on INSERT duplicate primary key in Oracle?

MERGE is the 'do INSERT or UPDATE as appropriate' statement in Standard SQL, and probably therefore in Oracle SQL too.

Yes, you need a 'table' to merge from, but you can almost certainly create that table on the fly:

 MERGE INTO Movie_Ratings M
       USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
          ON (M.mid = N.mid AND M.aid = N.aid)
       WHEN     MATCHED THEN UPDATE SET M.rating = N.rating
       WHEN NOT MATCHED THEN INSERT(  mid,   aid,   rating)
                             VALUES(N.mid, N.aid, N.rating);

(Syntax not verified.)

ON DUPLICATE KEY UPDATE throws SQL error ORA-00933: SQL command not properly ended

I am using Oracle Database.

Is it that it wont support 'ON DUPLICATE KEY UPDATE' clause ? If
No,what is the other option to achieve this ?

Probably it is a MySQL supported feature. Oracle doesn't support ON DUPLICATE KEY UPDATE. You need to use a MERGE statement.

For example,

MERGE INTO dest_table t
  USING (SELECT <column_list> 
           FROM source_table) s
     ON (t.col1 = s.col1) -- use required join keys
   WHEN MATCHED THEN
     UPDATE SET t.<column_list> = s.<column_list>
                ...
   WHEN NOT MATCHED THEN 
     INSERT( t.<column_list>)
       VALUES( s.<column_list>)

Read the documentation for more details on MERGE. Few examples here.

Update Based on OP's new query in the edited question.

WHEN NOT MATCHED THEN     INSERT(REPORT_ID,TITLE,CATEGORY,DISPLAY_ORDER,QUERY,DESCRIPTION,CONTENT_SEQ,DELD,ADMIN_ID)
       VALUES(27,
        'a',
        'z',
        9,
        'q',
        'd',
        1,
        0,
        1;

There is a closing brace missing in the end before the semi-colon. Try this:

MERGE INTO reports rt 
USING (SELECT report_id, 
              title, 
              category, 
              display_order, 
              query, 
              description, 
              content_seq, 
              deld, 
              admin_id 
       FROM   reports) rs 
ON (rt.report_id = rs.report_id) 
WHEN matched THEN 
  UPDATE SET rt.title = 'a', 
             rt.category = 'z', 
             rt.display_order = 9, 
             rt.query = 'q', 
             rt.description = 'd', 
             rt.content_seq = 1, 
             rt.deld = 0, 
             rt.admin_id = 1 
WHEN NOT matched THEN 
  INSERT(report_id, 
         title, 
         category, 
         display_order, 
         query, 
         description, 
         content_seq, 
         deld, 
         admin_id) 
  VALUES(27, 
         'a', 
         'z', 
         9, 
         'q', 
         'd', 
         1, 
         0, 
         1); 

Cx_Oracle equivalent of on duplicate key update

Is there anything like on duplicate key update

In Oracle, it is called MERGE; have a look at the following example:

Table contents at the beginning:

SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

MERGE statement:

SQL> merge into dept d
  2    using (select deptno, dname, loc
  3           from (select 10 deptno, 'ACC' dname, 'NY' loc from dual --> already exists, should be updated
  4                 union all
  5                 select 99       , 'NEW DEPT' , 'LONDON' from dual --> doesn't exists, should be inserted
  6                )
  7          ) x
  8  on (d.deptno = x.deptno)
  9  when matched then update set
 10                      d.dname = x.dname,
 11                      d.loc   = x.loc
 12  when not matched then insert (d.deptno, d.dname, d.loc)
 13                        values (x.deptno, x.dname, x.loc);

2 rows merged.

The result: as you can see, values for existing DEPTNO = 10 were updated, while the new DEPTNO = 99 was inserted into the table.

SQL> select * From dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACC            NY
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        99 NEW DEPT       LONDON

SQL>

I don't speak Python so I can't compose code you might use, but I hope that you'll manage to do it yourself.

convert mysql (on duplicate key ) query to oracle merge

To do this, you need to use a table or subquery in the using clause (in your case, you need a subquery).

In Oracle, you can use the dual table if you need to select something without needing to select from an actual table; this is a table that contains only a single row and a single column.

Your merge statement should therefore look something like:

MERGE INTO table1 tgt
USING (SELECT 'id' id,
          'dept_id' dept_id,
          'name' NAME,
          'description' description,
          'creation_time' creation_time,
          'modified_time' modified_time
     FROM   dual) src
ON tgt.id = src.id
WHEN MATCHED THEN
  UPDATE
  SET  tgt.dept_id       = src.dept_id,
       tgt.description   = src.description,
       tgt.name          = src.name,
       tgt.creation_time = src.creation_time,
       tgt.modified_time = src.modified_time
WHEN NOT MATCHED THEN
  INSERT
    (tgt.id,
     tgt.dept_id,
     tgt.name,
     tgt.description,
     tgt.creation_time,
     tgt.modified_time)
  VALUES
    (src.id,
     src.dept_id,
     src.name,
     src.description,
     src.creation_time,
     src.modified_time);

Note how the when not matched clause uses the columns from the source subquery, rather than using the literal values you supplied. (I assume that in your actual code, these literal values are actually variables; creation_time is a pretty odd value to store in a column labelled creation_time!).

I've also switched the aliases to make it clearer where you're merging to and from; I find this makes it easier to understand what the merge statement is doing. YMMV.

Insert ...on duplicate in oracle?

Look up the SQL standard MERGE statement, which is supported by (more recent versions of) Oracle. This will work with other DBMS than Oracle too.

---

Related Topics