5

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,
ollo
  • 926
  • 1
  • 14
  • 33
  • The keyword :"using ( ! parameters should go here somehow? )". Do i have to create a temporary table somehow and put that after the using or is there some other way to get my values into this merge sentence. And if there is no other way, how do i make a temp table? – ollo Apr 13 '12 at 14:01
  • No you don't need any temporary table... Check this link for merge syntax.http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm – Teja Apr 13 '12 at 14:03
  • I take it that i need to use a subquery. How do i create acceptable subquery with pure parameter values, Spring replaces :code with a normal value '123455'. Also this restriction screws me up:"You cannot update a column that is referenced in the ON condition clause." - since i want to update pr.code when :id isnt null.. – ollo Apr 13 '12 at 14:10
  • Can you post the complete query and the error you are getting... – Teja Apr 13 '12 at 14:12
  • That is the complete query as of now, i dont know how to finish it and dont have a workaround. The constraint problem i can probably figure out. I need to analyze if i even need to update pr.code or if it always matches the the DB value if :id isn't null. Workdays over for now :D ill get back to it later. – ollo Apr 13 '12 at 14:13

1 Answers1

7

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.

  • Thanks this almost works. The only problem that remains is that i cant update **pr.code** because im referencing it in the **ON** clause: `on (pr.id = t.id or pr.code = t.code)`. This isnt a problem if i enter the update with `pr.code = t.code`, but if i enter it with `pr.id = t.id` **pr.code** needs to be updated.. – ollo Apr 16 '12 at 11:25
  • Why is this constraint on the merge clause anyway..? – ollo Apr 16 '12 at 11:33