Testing to INSERT or UPDATE MySQL record

bradcathey has asked for the wisdom of the Perl Monks concerning the following question:

Fellow Monasterians,

In an attempt to simplify code, I'm trying to figure out a way to test whether or not to INSERT or UPDATE a table that will have only one row. If the table has never been saved to, I want it to INSERT, otherwise if it has the sole record, then UPDATE.

my %sql_data = (
   id        =>    1,
   message  => $message,
);

my $sql = qq/UPDATE bankend SET / . join(' = ?,', keys %sql_data) . qq
+/ = ? /;
                        
$sth = $dbh->prepare($sql) or die "prepare: $stmt: $DBI::errstr";
if (!$sth->execute(values %sql_data)) {
                        
   $sql = qq/INSERT INTO bankend (/ . join(',', keys %sql_data) . qq/)
+ 
      VALUES (/ . join(',', ('?') x keys %sql_data) . qq/)/;
                                                
   $sth = $dbh->prepare($sql) or die "prepare: $stmt: $DBI::errstr";
   $sth->execute(values %sql_data) or die "execute: $stmt: $DBI::errst
+r";
}
[download]

Right now, when I run it, it does neither—the table remains empty. As you see, I'm testing (I believe) for the error when it tries to update the "existing" row, when it actually has not been created. Thanks.

—Brad
"Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton

Comment on Testing to INSERT or UPDATE MySQL record Download Code

Replies are listed 'Best First'.
Re: Testing to INSERT or UPDATE MySQL record by Solo (Deacon) on Jan 18, 2005 at 18:21 UTC
Check out the MySQL REPLACE statement. --Solo -- You said you wanted to be around when I made a mistake; well, this could be it, sweetheart.	[reply]
Re: Testing to INSERT or UPDATE MySQL record by dragonchild (Archbishop) on Jan 18, 2005 at 18:25 UTC
In most databases, you would have to LOCK the tables SELECT determine whether to UPDATE or INSERT Do the action UNLOCK the tables MySQL has REPLACE, which does that for you atomically. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]
Re: Testing to INSERT or UPDATE MySQL record by borisz (Canon) on Jan 18, 2005 at 19:45 UTC
Beside the other advices your code has several errors. Your `update` statement has no `where` clausel and act on all rows. Since there are no rows in the table `execute` work fine, but return `0e0`. So there is no need to call `insert`. `my $r = $sth->execute(values %sql_data) or die "execute failed"; if ( $r == 0 ) { # do the insert }` [download] Boris	[reply] [d/l]
Re: Testing to INSERT or UPDATE MySQL record by periapt (Hermit) on Jan 18, 2005 at 21:03 UTC
You could also try a `$sql = qq\|SELECT COUNT() FROM bankend\|;` before the update/insert decision block. The program could then take the appropriate action depending on whether the result was 0, 1, or 2+ using a simple if-elsif-else ladder (for error checking). PJ use strict; use warnings; use diagnostics;*	[reply] [d/l]

use strict; use warnings; use diagnostics;