On Thu, 2002-03-28 at 12:26, FLAHERTY, JIM-CONT wrote:
> I made the column testname unique . But I did quite understand how to change
> to query syntax.
> Stephen told me to add where clause "Where IDENTIFIER NOT IN ( SELECT
> IDENTIFIER FROM TABLE)"
>
> I dont get what he means .. I am new to perl .
First you need to create a separation in your head: SQL and Perl. Perl
is a scripting language that happens to have a module called DBI that
lets you talk to databases in SQL (Structured Query Language). Your
problem lies not within Perl, but within your understanding of SQL and
consequently relational databases.
>
>
>
> the table
>
> show columns from phistory;
> +-----------+-------------+------+-----+---------+----------------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+-------------+------+-----+---------+----------------+
> | num | int(4) | | PRI | NULL | auto_increment |
> | testname | varchar(60) | YES | MUL | NULL | |
> | numpeople | int(6) | YES | | NULL | |
> | numtimes | int(6) | YES | | NULL | |
> | average | float | YES | | NULL | |
> +-----------+-------------+------+-----+---------+----------------+
> 5 rows in set (0.00 sec)
<snip />
#the follow query first gets a list of all testnames in the phistory
#table and then finds all entries in testhistory that do not match
#at least one of the testnames in the list gotten from phistory.
my $sth = $dbh->prepare(
# this is the identifier
# he talked about
# ||
# \/
"select distinct testname from testhistory where testname not in
(select distinct testname from phistory)"
) or die " unable to prepare query:" . $dbh->errstr;
$sth->execute or die " unable to execute query:" . $dbh->errstr;
foreach $row (@{$sth->fetchall_arrayref}) {
my($tname) = @$row;
#$dbh->quote knows how to quote character strings for
#the database it is connected to
#NOTE: this declaration of $sth masks the earlier one
#this $sth only lives inside the foreach loop
my $sth = $dbh->prepare(
'insert into phistory (testname) values (' .
$dbh->quote($tname) . ')')
or die " unable to prepare query:" . $dbh->errstr;
$sth->execute or die " unable to execute query:" . $dbh->errstr;
$sth->finish;
}
$sth->finish;
--
Today is Boomtime the 14th day of Discord in the YOLD 3168
Keep the Lasagna flying!
Missile Address: 33:48:3.521N 84:23:34.786W
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
