Upsert method 1: When you expect to be mostly adding data SQL

 


create table this_pk_probably_doesnt_exist (but_if_it_does integer primary key, i_need_to_update_other_cols integer);

var so_insert_first number;
var and_update_second number;

exec :so_insert_first := 1;
exec :and_update_second := 1;

-- insert the data
begin

insert into this_pk_probably_doesnt_exist (
but_if_it_does, i_need_to_update_other_cols
) values (
:so_insert_first, :and_update_second
);

exception
when DUP_VAL_ON_INDEX then

update this_pk_probably_doesnt_exist
set i_need_to_update_other_cols = :and_update_second
where but_if_it_does = :so_insert_first;

end;
/

select * from this_pk_probably_doesnt_exist;

exec :and_update_second := 2;

-- now update it
begin

insert into this_pk_probably_doesnt_exist (
but_if_it_does, i_need_to_update_other_cols
) values (
:so_insert_first, :and_update_second
);

exception
when DUP_VAL_ON_INDEX then
update this_pk_probably_doesnt_exist
set i_need_to_update_other_cols = :and_update_second
where but_if_it_does = :so_insert_first;
end;
/

select * from this_pk_probably_doesnt_exist;

drop table this_pk_probably_doesnt_exist 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)