Re: Existence Of Data

In article <4aa728$tbi_at_cisu2.jsc.nasa.gov>, proberts_at_isc901.jsc.nasa.gov says...
>
>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.

The original statement is using the primary key field in the where clause, that guarantees only one row will be returned.

-- 
Tony Noble
cz0763%zip02_at_mt.gov
Opinions expressed do not necessarily reflect those of my employer.