Upsert method 2: When you expect to mostly be updating existing data SQL

 


create table this_pk_probably_does_exist (so_check_if_anything_changed integer primary key, but_we_need_to_store_it integer);

var and_update_if_nothing_was number;
var if_it_doesnt number;

exec :and_update_if_nothing_was := 1;
exec :if_it_doesnt := 1;

begin

update this_pk_probably_does_exist
set but_we_need_to_store_it = :if_it_doesnt
where so_check_if_anything_changed = :and_update_if_nothing_was;

if sql%rowcount = 0 then
insert into this_pk_probably_does_exist (so_check_if_anything_changed, but_we_need_to_store_it)
values (:and_update_if_nothing_was, :if_it_doesnt);
end if;

end;
/

select * from this_pk_probably_does_exist;

exec :if_it_doesnt := 2;

begin
update this_pk_probably_does_exist
set but_we_need_to_store_it = :if_it_doesnt
where so_check_if_anything_changed = :and_update_if_nothing_was;

if sql%rowcount = 0 then
insert into this_pk_probably_does_exist (so_check_if_anything_changed, but_we_need_to_store_it)
values (:and_update_if_nothing_was, :if_it_doesnt);
end if;
end;
/

select * from this_pk_probably_does_exist;

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