0

I have an Oracle Table named Consumer with 10 columns where in column# 2, 3 and 4 constitutes the primary key for it. Now, I want to insert into this table through a Pandas Dataframe using Insert...On Duplicate Key Update SQL statement.

First, I am converting any pandas NaNs or NaTs to Oracle None and then converting the Dataframe rows to tuples for insertion. If there is a primary key violation during insertion then I need to update only the last 4 columns in the table.

The code which I am using here is as follows:

df1 = df.astype(object).where(df.notnull(), None)
rows = [tuple(x) for x in df1.values]

query = """INSERT INTO CONSUMER VALUES (:1,:2,:3, :4, :5, :6, :7, :8, :9, :10) ON DUPLICATE KEY UPDATE 
                                  DT = VALUES(:7),
                                  AT = VALUES(:8),
                                  OB = VALUES(:9),
                                  UT = VALUES(:10)"""

dbcur.executemany(query, rows)
dbcon.commit()

Where DT, AT, OB and UT are the names of the last 4 columns in the table. But this is giving me the following error:

cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended

Can someone please help me in finding out and correcting whats wrong with my code? Many thanks in advance.

Prachi
  • 494
  • 3
  • 8
  • 21
  • Does this answer your question? [Oracle: how to UPSERT (update or insert into a table?)](https://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table) – Mat Feb 13 '20 at 16:58
  • `@Mat` I am afraid ...but no. I need to do it using 'Insert..on Duplicate key' only. Could you please help me in rectifying the error? – Prachi Feb 13 '20 at 17:01
  • 2
    Then switch to a database engine that supports that syntax. – Mat Feb 13 '20 at 17:02
  • `@Mat` I didnt understand your reply. Could you please elaborate? The DB I am using is Oracle12.2. – Prachi Feb 13 '20 at 17:04

1 Answers1

2

The INSERT ... ON DUPLICATE KEY UPDATE ... syntax does not exist in Oracle (it is specific to MySQL). Oracle's equivalent is the MERGE syntax, which for your use case would look like:

merge into consumer
using dual
on (col2 = :2 and col3 = :3 and col4 = :4)
when not matched then insert values (:1,:2,:3, :4, :5, :6, :7, :8, :9, :10)
when matched then update set dt = :7, at = :8, ob = :9, ut = :10

Note: you did not tell what the names of the primary key columns are, so I assumed col2/3/4. It would also be good to enumerate all columns for insert in the not matched clause of the query.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • `@GMB` thanku for your reply but when I am excecuting this its giving me `cx_Oracle.DatabaseError: ORA-01008: not all variables bound`. I just cant seem to understand what is the problem. – Prachi Feb 14 '20 at 04:41
  • `@GMB` Also will this query work for the very first row insertion into the otherwise empty db table? – Prachi Feb 14 '20 at 04:43
  • @Prachi: make sure that you do pass as many parameters as defined in the query (you need 10). Yes this works when the table is emplty (an insert occurs). – GMB Feb 14 '20 at 10:08