oracel insert or update based on existance

Need some help coding an oracle statement that checks for existence and inserts if not present or updates when present.
x00005Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
what version?  if 9i or above use MERGE
If 8 or below you'll have to use two separate statements

usually it's easiest to simply try one (try the one you think will succeed most often) and then capture exceptions, and then try the other, rather than adding an additional step to query first.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ritesh_GargCommented:
You may use the example below as reference:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm#SQLRF01606
MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

Open in new window

0
SujithData ArchitectCommented:
A simple example.
SQL> select * from tbl1;
 
        ID VAL
---------- --------------------
       100 test
 
1 row selected.
 
SQL> begin
  2   update tbl1
  3   set val = 'new'
  4   where id = 200;
  5  
  6   if SQL%ROWCOUNT = 0 then
  7    insert into tbl1 values(200, 'new');
  8   end if;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from tbl1;
 
        ID VAL
---------- --------------------
       100 test
       200 new
 
2 rows selected.
 
SQL> begin
  2   update tbl1
  3   set val = 'changed'
  4   where id = 200;
  5  
  6   if SQL%ROWCOUNT = 0 then
  7    insert into tbl1 values(200, 'changed');
  8   end if;
  9  end;
 10  /
 
PL/SQL procedure successfully completed.
 
SQL> select * from tbl1;
 
        ID VAL
---------- --------------------
       100 test
       200 changed
 
2 rows selected.
 
SQL> 

Open in new window

0
Naveen KumarProduction Manager / Application Support ManagerCommented:

You can also use exists operator to check whether a record exists in a table for a given criteria. So
we can use that while inserting saying if not exists then do the insert otherwise don't need to insert.

create table bb(a number, b varchar2(20))

insert into bb values(1,'one inserted value'); -- inserts record as many times as we run

insert into bb values(2,'two inserted value');  -- inserts record as many times as we run

-- the below statement will insert one record for the first time but if you execute it for the
-- second time, it will not insert a record again because of the exists check.
insert into bb select 3 ,'three inserted value' from dual where not exists ( select 1 from bb where a = 3 );

-----------------------------------------

The below is another example on how to use exists while doing an update to check whether records exists in a table or not for a given condition.

update dept x
 set dept_count = ( select count(1) from emp where deptno = x.deptno )
 where dname ='Marketing'
 and exists ( select 1 from emp y where x.deptno = y.deptno ) ;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.