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

I have the below working insert statement, where I am essentially mapping the column data from an uploaded .csv file through a loop into my VALUES variables below…

All worked fine, except now I am just trying to add a tweak INSERT or UPDATE – if ‘app_id‘ first column, value is found in the uploaded .csv, i.e. duplicate is found – simply update the identified duplicate row..

Based on some research ON DUPLICATE KEY UPDATE is the way to go, but I don’t understand how/where I define the key? – you can see below I tried adding the ON DUPLICATE KEY UPDATE to the end of the query with my variables again, but I’m pretty sure this can’t be correct.

$sql = "INSERT INTO app.also_data (app_id, fac_ident, lg_name, basic_tp, catc_vd, address, assoc_city, latitude, longitude, assoc_cnty, assoc_st, time_zone, dd_reg, ato_sa, ato_td, fema_reg, ops_hrs, prim_ph, atc_level, tower_type, manager, sat_phone_num, td_mgr, to_dist, tod_mgr, stof_fac, ops_status, crt_rl, created_user, date)
                      VALUES ('${data[0]}', '${data[1]}', '${data[2]}', '${data[3]}', '${data[4]}', '${data[5]}', '${data[6]}', '${data[7]}', '${data[8]}', '${data[9]}', '${data[10]}', '${data[11]}', '${data[12]}', '${data[13]}', '${data[14]}', '${data[15]}', '${data[16]}', '${data[17]}', '${data[18]}', '${data[19]}', '${data[20]}', '${data[21]}', '${data[22]}', '${data[23]}', '${data[24]}', '${data[25]}', '${data[26]}', '${data[27]}', '${data[28]}', '${data[29]}') ON DUPLICATE KEY UPDATE ('${data[0]}', '${data[1]}', '${data[2]}', '${data[3]}', '${data[4]}', '${data[5]}', '${data[6]}', '${data[7]}', '${data[8]}', '${data[9]}', '${data[10]}', '${data[11]}', '${data[12]}', '${data[13]}', '${data[14]}', '${data[15]}', '${data[16]}', '${data[17]}', '${data[18]}', '${data[19]}', '${data[20]}', '${data[21]}', '${data[22]}', '${data[23]}', '${data[24]}', '${data[25]}', '${data[26]}', '${data[27]}', '${data[28]}', '${data[29]}')";

Note: The above was working fine for Insert minus my below attempt adding:

... ON DUPLICATE KEY UPDATE ('${data[0]}', '${data[1]}', '${data[2]}', '${data[3]}', '${data[4]}', '${data[5]}', '${data[6]}', '${data[7]}', '${data[8]}', '${data[9]}', '${data[10]}', '${data[11]}', '${data[12]}', '${data[13]}', '${data[14]}', '${data[15]}', '${data[16]}', '${data[17]}', '${data[18]}', '${data[19]}', '${data[20]}', '${data[21]}', '${data[22]}', '${data[23]}', '${data[24]}', '${data[25]}', '${data[26]}', '${data[27]}', '${data[28]}', '${data[29]}')";

So how can I add ON DUPLICATE KEY UPDATE based off of column app_id correctly – if duplicate app_id value is found in the insert, simply update instead?

Answer

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)

Advertisement

Answer