Upsert method 3: The set based approach SQL

 


create table rows_im_not_sure_exist_or_not (values_matching integer primary key, the_non_key_columns_to integer);

var this_passed_pk number;
var the_passed_data_otherwise number;

exec :this_passed_pk := 1;
exec :the_passed_data_otherwise := 1;

-- insert the data
merge into rows_im_not_sure_exist_or_not r
using dual
on (r.values_matching = :this_passed_pk)
when matched then
update set r.the_non_key_columns_to = :the_passed_data_otherwise
when not matched then
insert (values_matching, the_non_key_columns_to)
values (:this_passed_pk, :the_passed_data_otherwise);

select * from rows_im_not_sure_exist_or_not;

exec :the_passed_data_otherwise := 2;

-- now update it
merge into rows_im_not_sure_exist_or_not e
using dual i
on (e.values_matching = :this_passed_pk)
when matched then
update set e.the_non_key_columns_to = :the_passed_data_otherwise
when not matched then
insert (values_matching, the_non_key_columns_to)
values (:this_passed_pk, :the_passed_data_otherwise);

select * from rows_im_not_sure_exist_or_not;

drop table rows_im_not_sure_exist_or_not purge;

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)