Re: Existence Of Data

From: Paul Roberts <proberts_at_isc901.jsc.nasa.gov>
Date: 1995/12/08
Message-ID: <4aa728$tbi_at_cisu2.jsc.nasa.gov>#1/1


Heather Dyson asks:
>
> Existence Of Data
>
> Heather Dyson <heather.dyson_at_template.com>
> 7 Dec 1995 15:47:16 GMT
> Template Software, Herndon, VA
>
> Newsgroups:
> comp.databases.oracle
>
> 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?

You might have a number of rows, instead of just one! If you accidentally pull up 20K rows, you could be a while waiting on your answer.

The quickest solution is:

SELECT "Y" FROM DUAL WHERE EXISTS
(SELECT <fields> FROM <table> WHERE <primary key> = <value>);

This query will find the first oneand quit looking (returning the value "Y").

You can set <a variable> to "N", and then SELECT "Y" INTO <a variable> and check its value.

If you're using PL/SQL, you can do the select without a variable and catch it with an exception handler -- code for the NO_DATA_FOUND exception.

Good Luck!

    _   _  _ _  __    : Paul Roberts                 
   |_  |_|  |  |      : Programmer / Analyst        
    _| | | _|_ |__    : proberts_at_isc901.jsc.nasa.gov 

Science Applications International Corporation

   (An employee-owned company)

  The opinions expressed are my own, and not necessarily   the views of SAIC, NASA, or the U.S. government. Received on Fri Dec 08 1995 - 00:00:00 CET

Original text of this message