oracle sql update if exists else insert

Question

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

User · Answer

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

User · Answer

The way I always do it  assuming the data is never to be deleted  only inserted  is to   Firstly do an insert  if this fails with a unique constraint violation then you know the row is there  Then do an update   Unfortunately 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

User · Answer

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

User · Answer

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    lt  lt update mytable gt  gt    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

User · Answer

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

User · Answer

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

[sql] oracle sql: update if exists else insert

Examples related to sql

Examples related to oracle