Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to do update if row exists, else insert (Oracle 8i, Java)

Posted on 2005-04-27
12
Medium Priority
?
4,803 Views
Last Modified: 2012-06-27

Hi,

Assume table TABLE1 with columns ID and VALUE. If an ID exists, I would like to update the VALUE, else insert the new VALUE with a new ID. How can I do this with Oracle 8i and Java? The solution can be with an SQL query, a stored procedure or Java code, but it must be transactionally safe and must not use exception handling for flow control.


0
Comment
Question by:rakhare
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 12

Expert Comment

by:geotiger
ID: 13879964
If you have your new data in a source table - source_table with the same column name, then

Update the table first:

update table1 a set value = select value from source_table where id = a.id
/

Then insert new records:

insert into table1
select id, value from source_table
where id not in (select distinct id from table1)
/
0
 
LVL 5

Expert Comment

by:KeirGordon
ID: 13880011
Typically if you need to do this you have your application structured improperly.

If a user is submitting something that causes an insert, then you should first do a query to see if it exists, and if it does you would throw an error to the user or tell them that it alread exists, then if it didnt exist you would go ahead and do an insert.

Anyway, in Oracle 8i it is easiest to do two seperate queries, do a select to see if its there, then an insert.

0
 

Author Comment

by:rakhare
ID: 13880050

geotiger,

What is source_table? Is it a special kind of table? I am not moving data within tables, but java code does SELECT/UPDATE/INSERT/DELETE via jdbc.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:rakhare
ID: 13880102
KeirGordon,

>If a user is submitting something that causes an insert, then you should first do a query to see if it exists, and if it does you would throw an error to the user or tell >them that it alread exists, then if it didnt exist you would go ahead and do an insert.

The user does not need to know anything. The code must do it. The java code can use a try/catch block to do the above, but generally it is not a good idea to use it to control the logic. As I have stated, the solution must not use exception handling for flow control.

>Anyway, in Oracle 8i it is easiest to do two seperate queries, do a select to see if its there, then an insert.

What if in between the 2 queries, some other user process inserts the row? Then the insert will fail. I need a fail safe solution.
0
 
LVL 5

Expert Comment

by:allanau20
ID: 13881816
Update if record exist or insert if record doesn't exist in one sweep.

CREATE PROCEDURE DBO.Sproc01 (@passfield01 AS CHAR(120), @passfield02 AS CHAR(120)) AS
BEGIN
    DECLARE @ID INT

    SELECT @ID = IdentityCol FROM TABLE1
    WHERE field01 = @passfield01 AND field02 = @passfield02 .. blah blah blah

    IF @ID IS NOT NULL
      UPDATE Statement goes here
    ELSE

      INSERT Statement goes here

END
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13883167
See the example below,

begin
for c1 in (select * from emp) loop
    -- Update if record is found.
    update emp_1 set ename = c1.ename
    where empno = c1.empno;
    --If no record found then, Insert.
    if SQL%ROWCOUNT = 0 then
       insert into emp_1 (empno, ename) values (c1.empno, c1.ename);
    end if;
end loop;
commit;
end;

Sujit
0
 

Author Comment

by:rakhare
ID: 13885582
allanau20,

If just after the SELECT and before the IF:

- if  @ID IS NOT NULL and the row is deleted, will the UPDATE fail ?
- if  @ID IS NULL and an identical row is inserted, will the INSERT fail ?

sujit_kumar,

what happens to your solution for similar situations?

Thanks.
0
 
LVL 12

Expert Comment

by:geotiger
ID: 13885638
Rakhare,

The source_table is just the table name that you would store your new records. You can use any other name.

You can just insert all your new records in the table, then issue the two sql statements that I provide through JDBC.
0
 
LVL 5

Expert Comment

by:allanau20
ID: 13889733
Sorry rakhare - I'm not too familiar with Oracle, I work with MS SQL. I think you should listen to the guy who lives in 'Oracle Land' geotiger. Good luck.
0
 
LVL 2

Accepted Solution

by:
Cezariusz earned 1200 total points
ID: 13892639
create table my_table (key integer, value varchar2(100))

create or replace procedure insert_or_update(p_key integer, p_value varchar2) as
  v_temp integer;
begin
  for r in (select key from my_table where key = p_key for update) loop
    -- row exists
     update my_table set key = p_key, value = p_value where key = p_key;
     dbms_output.put_line('Updated key ' || to_char(p_key));
     return;
  end loop;
  -- row doesn't exist
  insert into my_table (key, value) values (p_key, p_value);
  dbms_output.put_line('Inserted key ' || to_char(p_key));
end;

begin
  insert_or_update(1, 'one');
  insert_or_update(1, 'other');
  insert_or_update(2, 'two');
end;

> Inserted key 1
> Updated key 1
> Inserted key 2

select * from my_table

KEY     VALUE
1     other
2     two
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

578 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