There are several ways to avoid storing duplicate rows in an Oracle database when inserting and updating data. In the examples I use a table with two columns that should be unique:
1.
Unique keys
ALTER TABLE tableName
ADD CONSTRAINT uniqueKeyName UNIQUE(columnName1, columnName2);
2.
First select and then insert or update
procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)
is
cursor c1(v1 in number, v2 in number) is
select '1' from tableName
where columnName1 = v1
and columnName2 = v2;
dummy varchar2(1);
begin
open c1(p_key1, p_key2);
fetch c1 into dummy;
if sql%found then
update tableName
set valueColumn = p_value
where columnName1 = p_key1
and columnName2 = p_key2;
else
insert into tableName (columnName1, columnName2, valueColumn)
values (p_key1, p_key2, p_value);
end if;
end;
/
3.
The MERGE-statement
MERGE INTO tableName tn
USING dual on (dual.dummy is not null
and tn.columnName1 = p_key1
and tn.columnName2 = p_key2)
WHEN MATCHED THEN
UPDATE set tn.valueColumn = p_value
WHEN NOT MATCHED THEN
insert (columnaName1, columnName2, valueColumn)
values (p_key1, p_key2, p_value);
4.
update and then insert
procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)
is
begin
update tableName
set valueColumn = p_value
where columnName1 = p_key1
and columnName2 = p_key2;
if sql%rowcount = 0 then
insert into tableName (columnName1, columnName2, valueColumn)
values (p_key1, p_key2, p_value);
end if;
end;
/
or
update tableName
set valueColumn = p_value
where columnName1 = p_key1
and columnName2 = p_key2;
insert into tableName (columnName1, columnName2, valueColumn)
select p_key1, p_key2, p_value
from dual
where not exists (select '1'
from tableName
where columnName1 = p_key1
and columnName2 = p_key2);
5.
first delete and then insert
procedure insertRow(p_key1 in number, p_key2 in number, p_value in number)
is
begin
delete from tableName
where columnName1 = p_key1
and columnName2 = p_key2;
insert into tableName (columnName1, columnName2, valueColumn)
values (p_key1, p_key2, p_value);
end;
/
