Re: Existence Of Data

Heather Dyson (heather.dyson_at_template.com) wrote:
: I am writing a program where I get some data and need to determine whether
: to do an update or an insert. So I have to check to see if the row already
: exists, if it does I do an update otherwise I insert.
 

: Is the best way to check for existence to do the following sql statement:
 

: select <fields> from <table> where <primary key> = <value>;
 

: Then if a row comes back I do an update and if no row comes back I do
: an insert. Or does anybody have a better way to check for existence?

Yeah, your method will work okay. Retrieve the rowid in the select statement. Use the rowid in the where clause of the update statement to make it faster.

Alternately,
 Just write an update statement using the data you have. Then, check the  variable SQL%ROWCOUNT. If it is greater than 0, it means the data existed.  If not, you'll need to insert the data. In this method you are eliminating  the select statement.

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different. **
---------------------------------------------------------------------------