aspose file tools*
The moose likes Oracle/OAS and the fly likes How to Determine Whether to Insert or Update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "How to Determine Whether to Insert or Update" Watch "How to Determine Whether to Insert or Update" New topic
Author

How to Determine Whether to Insert or Update

Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1129

Folks,
First some background, then my question.

I am using Oracle 9i (9.2.0.4) on SUN [sparc] Solaris 9

I have a (parent) table:

create table PARENT (ID number primary key);

And a child table:

create table CHILD (PARENT_ID number, ORDERING number,
primary key (PARENT_ID, ORDERING),
foreign key PARENT_ID references PARENT (ID))

I have a PL/SQL procedure that updates the parent table, and the child table. The procedure receives a string containing a delimited list of database table and column names and values that are used to locate and update a single row in table PARENT as well as any associated rows in table CHILD. If a row doesn't exist in table CHILD, a new row is created and if the row does exist, it is updated.

The string parameter (that is passed to the procedure) is parsed and SQL statements are generated according to which tables, columns and values are found. Native dynamic SQL is used to execute the generated SQL statements.

There is a one-to-many relationship between PARENT and CHILD. In other words, there may be zero, one or several (but never more than three) rows in table CHILD with the same PARENT_ID column value.

I am looking for the most efficient way to determine whether I need to insert into or update table CHILD. Currently (and this is code I inherited -- I didn't write it originally) I have this:
[NOTE: This is a very simplified version of my code, for illustration only.]

Thanks (in advance) for any suggestions,
Avi.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30352
    
150

Avi,
That is the best way to do it. The only thing I would change is to make the query "select count(*) into L_COUNT". This could execute slightly faster depending on which is column 1. If column 1 is in the index, the execution speed would be the same, but the wildcard still protects you from future schema changes.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
Don't get me started about those stupid light bulbs.
 
subject: How to Determine Whether to Insert or Update