Possible Duplicate:
Oracle: how to UPSERT (update or insert into a table?)
Hi,
I have a table in which a record has to be modified if it already exists else a new record has to be inserted.
Oracle sql doesnt accept IF EXISTS, otherwise I would have done an if - update - else - insert query. I've looked at MERGE but it only works for multiple tables. What do i do?
MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:
MERGE INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);
Alternatively you can do this in PL/SQL:
BEGIN
INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE mytable
SET name = 'x'
WHERE id = 1;
END;
MERGE instruction, but as for the exception DUP_VAL_ON_INDEX handling, that is definitely a nice solution, knowing that Oracle exception handling is regularly used for such behaviour! =)
MERGE INTO statement, make sure to commit the transaction by running COMMIT;merge into MY_TABLE tgt
using (select [expressions]
from dual ) src
on (src.key_condition = tgt.key_condition)
when matched then
update tgt
set tgt.column1 = src.column1 [,...]
when not matched then
insert into tgt
([list of columns])
values
(src.column1 [,...]);
You could use the SQL%ROWCOUNT Oracle variable:
UPDATE table1
SET field2 = value2,
field3 = value3
WHERE field1 = value1;
IF (SQL%ROWCOUNT = 0) THEN
INSERT INTO table (field1, field2, field3)
VALUES (value1, value2, value3);
END IF;
It would be easier just to determine if your primary key (i.e. field1) has a value and then perform an insert or update accordingly. That is, if you use said values as parameters for a stored procedure.
update touches zero rows so you assume there is no row and need to do an insert, but in the mean-time someone has done an insert so your insert fails with a unique constraint violation. That's why it's important to do insert (and catch unique constraint violations) then update, not the other way around.
The way I always do it (assuming the data is never to be deleted, only inserted) is to
insert, if this fails with a unique constraint violation then you know the row is there,updateUnfortunately many frameworks such as Hibernate treat all database errors (e.g. unique constraint violation) as unrecoverable conditions, so it isn't always easy. (In Hibernate the solution is to open a new session/transaction just to execute this one insert command.)
You can't just do a select count(*) .. where .. as even if that returns zero, and therefore you choose to do an insert, between the time you do the select and the insert someone else might have inserted the row and therefore your insert will fail.
HC-way :)
DECLARE
rt_mytable mytable%ROWTYPE;
CURSOR update_mytable_cursor(p_rt_mytable IN mytable%ROWTYPE) IS
SELECT *
FROM mytable
WHERE ID = p_rt_mytable.ID
FOR UPDATE;
BEGIN
rt_mytable.ID := 1;
rt_mytable.NAME := 'x';
INSERT INTO mytable VALUES (rt_mytable);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
<<update_mytable>>
FOR i IN update_mytable_cursor(rt_mytable) LOOP
UPDATE mytable SET
NAME = p_rt_mytable.NAME
WHERE CURRENT OF update_mytable_cursor;
END LOOP update_mytable;
END;
Please refer to this question if you want to use UPSERT/MERGE command in Oracle. Otherwise, just resolve your issue on the client side by doing a count(1) first and then deciding whether to insert or update.
