CODEWITHSUNDEEP
sqloracle-databaseif-statement
Cagdas Sancarbarlaz
Cagdas Sancarbarlaz

Reputation: 3

If not exist for insert or update on Oracle

I want to insert a row , if the row does not exists and if it exists, I want to update the row. I am currently using PLSql Oracle. When I want to write a query like this; 

IF NOT EXIST (SELECT * FROM TABLE_NAME WHERE ID=1)
   INSERT INTO TABLE_NAME(ID,NAME)VALUES(1,'CAGDAS SANCARBARLAZ')
ELSE
   UPDATE TABLE_NAME SET NAME='UGUR CAN' WHERE ID=1

How can I do that in Oracle Sql ?

Upvotes: 0

Views: 309

Answers (2)

user330315
user330315

Reputation:

Use a MERGE statement. Providing the constant values is a bit messy, because Oracle does not support the standard VALUES constructor, so you need to use a SELECT ... FROM DUAL for the source of the MERGE statement:

merge into table_name
using (
  select 1 as id, 'CAGDAS SANCARBARLAZ' as name from dual
) t on t.id = table_name.id
when matched then update
   set name = 'UGUR CAN'
when not matched then insert (id, name) values (t.id, t.name);

Upvotes: 0

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

Use MERGE for that.

MERGE INTO TABLE_NAME T
   USING (SELECT 1 ID from dual) S
   ON (T.ID = S.ID)
   WHEN MATCHED THEN UPDATE SET T.NAME='UGUR CAN'
   WHEN NOT MATCHED THEN INSERT (ID, NAME) VALUES (1, 'CAGDAS SANCARBARLAZ');

Upvotes: 1

Related Questions