Oracle SQL merge statement with only 1 table and a bunch of values

merge-statement, oracle, spring, sql

Solution

This should work:

merge into proj.person_registry pr
using ( 
  select 42 as id
         'xyz' as code,
         'Dent' as name,
         'Arthur' as firstname,
         'male' as cl_gender
         'closed' as cl_status,
         'Somewher' as aaddress,
         current_date as aaddress_date
   from dual
) t on (pr.id = t.id or pr.code = t.code)
when matched then 
update set pr.code             = t.code,
        pr.name                 = t.name,
        pr.firstname            = t.firstname,
        pr.cl_gender            = t.cl_gender,
        pr.cl_status            = t.cl_status,
        pr.aadress              = t.aadress,
        pr.aadress_date         = t.aadress_date 
when not matched then
insert values (t.code, t.name, t.firstname, t.cl_gender, t.cl_status, ;aadress, t.aadress_date);

I'm not familiar with Spring's JDBC template, but replacing the actual values in the `select ... from dual` query by parameter placeholders should work.

Problem

I'm using Spring JDBC and oracle SQL. using the SpringJDBC class MapSqlParameterSource, i have mapped the data i want to merge. Now i want to use the merge statement to update/insert database table. All i have is one table and a bunch of parameters i want to merge into it. ``` merge into proj.person_registry pr using ( ! parameters should go here somehow? ) on (pr.id = :id or pr.code = :code) when matched then update set pr.code = :code, pr.name = :name, pr.firstname = :firstname, pr.cl_gender = :cl_gender, pr.cl_status = :cl_status, pr.aadress = :aadress, pr.aadress_date = :aadress_date when not matched then insert values (:code, :name, :firstname, :cl_gender, :cl_status, ;aadress, :aadress_date); ``` Do i have to somehow create a temporary table for the using keyword or is there another way? how would i go about merging like this? also there are two unique keys pr.id and pr.code. Sometimes the parameter :id is null, when this happens I want to reach the update statement based on pr.code getting matched to :code. Will it still work if my update contains the line: ``` update set pr.code = :code, ```

Original source