I'm pretty sure the exists keyword comes into play some how so that it stops
processing results once a row is returned but I'm not sure exactly how.
This code:
my $statement = q{select 1 from x where exists xyz = ?};
doesn't work in Sybase or Oracle.
However, in terms of pure SQL, these will work (and possibly be doing what
I'm thinking of? which is to lookup the existence of a key with the least
possible amount of work done):
ORACLE::
select 1 from dual where exists
( select * from some_table where some_column = 'some_value')
SYBASE::
select 1 where exists
( select * from some_table where some_column = 'some_value')
Anyway, it's obviously not as portable as any of the other methods, but I'm
just trying to brainstorm the possibilities of the best methods for an
existence lookup.
> -----Original Message-----
> From: Thomas A. Lowery [mailto:[EMAIL PROTECTED]]
> Sent: Sunday, July 08, 2001 9:18 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Checking for the existence of a certain row.
>
>
> > my $statement = q{select 1 from x where exists xyz = ?};
>
> Two things, this syntax does not with Oracle. Also, unless you've
> selected the row(s) for update, than I believe it stops on the first
> occurrence. The where clause, primary/unique indexes, would play a
> part.
>
> Tom
>
>
> On Sat, Jul 07, 2001 at 05:02:02PM -0700, Tim Harsch wrote:
> > Wouldn't that hit every row in the table where xyz = ?, if so could you
> > short circuit it with an exists or something:
> >
> > my $statement = q{select 1 from x where exists xyz = ?};
> >
> > > -----Original Message-----
> > > From: Thomas A. Lowery [mailto:[EMAIL PROTECTED]]
> > > Sent: Saturday, July 07, 2001 4:57 PM
> > > To: [EMAIL PROTECTED]
> > > Subject: Re: Checking for the existence of a certain row.
> > >
> > >
> > > What I do is:
> > >
> > > my $statement = q{select 1 from x where xyz = ?};
> > >
> > > Prepare:
> > >
> > > my $exist_sth = $dbh->prepare( $statement );
> > >
> > > Then call selectrow_array with a scalar:
> > >
> > > $does_exists = $dbh->selectrow_array($statement, undef,
> > > $key_to_check );
> > >
> > > Tom
> > >
> > >
> > > On Fri, Jul 06, 2001 at 05:08:56PM -0400, James Kufrovich wrote:
> > > > Hi.
> > > >
> > > > I'd like to check for the existence of data in a
> row of a database
> > > > (MySQL), if given the value of a primary key. ("Is there
> > > already a row in
> > > > the database that has this value as a key?") I don't care what
> > > data is in
> > > > the row, or if more than one row (!!) is found. I'd hope
> that whatever
> > > > method I use will stop searching the database after it
> finds the first
> > > > match.
> > > >
> > > > What would be the best way to go about this? Is
> there a special
> > > > method that can do this, or would I have to use selectrow_array (or
> > > > fetchrow_array or one of those) and then see if it finds
> anything? I
> > > > suppose I can call 'finish' as soon as a match is found, if the
> > > method (or
> > > > whatever) doesn't stop by itself. I'd appreciate any tips. Thanks.
> > > >
> > > > Jamie Kufrovich
> > > >
> > > > Egg, eggie (at) sunlink (dot) net
> > > > FMSp3am/MSp3am A- C+ D H+ M+ P+++ R+ T W Z+
> > > > Sp#/p++ RL->CT a+ cl++ d? e++ f h* i+ j p+ sm+
> > >
> > > --
> > > Thomas A. Lowery
> > > See DBI/FAQ http://tlowery.hypermart.net
> > >
> > >
>
> --
> Thomas A. Lowery
> See DBI/FAQ http://tlowery.hypermart.net
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
>
