(jeffa) Re: DBI multiple prepares

Just a thought for you: if you don't really need to know whether or not the row you are attempting to update exists or not, then just update it:

my $sth = $dbh->prepare("
   UPDATE many_address
   SET name=NULL, date_loaded=NULL
   WHERE name=? AND number=? AND street=?
");

while (<RA>) {
   my ($name,$add,$street) = split(/\|/,$_);
   chomp($street);
   $sth->execute($name,$add,$street);
}
[download]

SQL doesn't care. If the name, address, and street don't match any existing record, then the update will not happen. Of course, for proper error checking my suggestion may not be helpful, but i still thought i should put this thought in your mind anyway. ;)

P.S. Get out of the habit of using NULL now, before it is too late! Use another field named 'status' if you are wanting to 'delete' a record without removing it from the table.

UPDATE:
Well, there is a way to find out if a row was updated or not, but you have to use do() instead, thus giving up your placeholders. From the docs:

$rows_affected = $dbh->do("UPDATE your_table SET foo = foo + 1");
[download]

UPDATE x 2:
Thanks iburrell and mpeppler. execute indeeds returns the number of rows updated or the value 0E0 (which means zero but ~~not~~ true - typos: sigh, thanks grantm!) if no rows were updated (it will return -1 if there is an error). Here is an update of the above code:

...

while (<RA>) {
   my ($name,$add,$street) = split(/\|/,$_);
   chomp($street);
   my $ret = $sth->execute($name,$add,$street);
   print "no update for $name\n" if $ret == 0;
}
[download]

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
B--B--B--B--B--B--B--B--
H---H---H---H---H---H---
(the triplet paradiddle with high-hat)

Comment on (jeffa) Re: DBI multiple prepares Select or Download Code

Replies are listed 'Best First'.
Re: (jeffa) Re: DBI multiple prepares by iburrell (Chaplain) on Nov 04, 2002 at 17:17 UTC
It is possible to use placeholders with `do`. The syntax is: `$rows = $dbh->do("UPDATE your_table SET foo = ?", undef, $foo);` [download] However, you don't need to use `do` though. `execute` returns the number of rows updated for a non-SELECT statement.	[reply] [d/l] [select]
Re: (jeffa) Re: DBI multiple prepares by mpeppler (Vicar) on Nov 05, 2002 at 00:48 UTC
Well, there is a way to find out if a row was updated or not, but you have to use `do()` Normally `execute()` returns the number of rows affected for update/delete/insert statements. Certainly that's what `do()` uses to return the value to the caller. Michael	[reply] [d/l] [select]