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,
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With