?
Solved

oracel insert or update based on existance

Posted on 2008-11-19
6
Medium Priority
?
897 Views
Last Modified: 2012-05-05
Need some help coding an oracle statement that checks for existence and inserts if not present or updates when present.
0
Comment
Question by:x00005
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 256 total points
ID: 22996624
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
 
LVL 6

Assisted Solution

by:Ritesh_Garg
Ritesh_Garg earned 248 total points
ID: 22997021
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
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 248 total points
ID: 23000581
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 248 total points
ID: 23001831

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question