Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Database Update or Insert

by Mr. Muskrat (Canon)
on Apr 26, 2002 at 17:45 UTC ( [id://162350]=CUFP: print w/replies, xml ) Need Help??

Sometimes you have some data to update but someone slips in some stuff thats not in the database yet. Well, you could explicitly check if it's there using a SELECT or you could do something like this...
my $sth = $dbh->prepare("UPDATE $table SET upc=$upc WHERE item_id=$ite +m_id;"); die "DBH Error:" . $dbh->errstr . "\n" if (!$sth); if (!$sth->execute) { # Oops! $item_id doesn't exist in $table so add it. my $sth = $dbh->prepare("INSERT INTO $table (item_id, upc) VALUES( +$item_id, $upc);"); die "DBH Error:" . $dbh->errstr . "\n" if (!$sth); die "STH Error:" . $sth->errstr . "\n" if (!$sth->execute); }

Replies are listed 'Best First'.
Re: Database Update or Insert
by VSarkiss (Monsignor) on Apr 26, 2002 at 18:46 UTC

    The biggest problem with this is what jsprat has pointed out above: you're counting on only getting primary key violations. You should at least check the error condition coming back from the update before blindly trying to insert the row.

    The second problem is that you're not taking advantage of one of DBI's best features: placeholders. You're taking the time to prepare the statement, but binding the values directly, which can be a problem if things need quoting. Here's how it would look:

    my $sth = $dbh->prepare("UPDATE $table SET upc=? WHERE item_id=?"); # ... if ($sth->execute($upc, $item_id)) { # ...

    Some style issues: you're re-declaring $sth in the inner block, masking the outer one, which is unnecessary and can cause confusion in a larger block of code. Finally -- this is just a matter of personal style -- but I find "die-if-not-command" puts the emphasis on the wrong thing. The important part is the command, not the error recovery, which comes through more clearly in this case with "command or die" style.

Re: Database Update or Insert
by jsprat (Curate) on Apr 26, 2002 at 18:03 UTC
    Normally, I wouldn't recommend this. It will work when the only cause of a failed update is that the data doesn't exist.

    What if the row (page, table) is locked? Do you still want to insert, or update?

Re: Database Update or Insert
by Mr. Muskrat (Canon) on Apr 26, 2002 at 21:45 UTC
    Alright, I'm not going to say that it's finished but it's a lot better than it was...
    foreach (<>) { my ($item_id, $upc) = split(",",$_); my $status = upsert($dbh, $table, $item_id, $upc); print "Item #: $item_id, UPC: $upc, Status: $status\n"; } sub upsert { my ($rows, $rows2, $status); my $true = "0E0"; my ($dbh, $table, $item_id, $upc) = @_; my $sth = $dbh->prepare("UPDATE $table SET upc=? WHERE item_id=?;") +or die $dbh->errstr; my $sth2 = $dbh->prepare("INSERT INTO $table (item_id, upc) VALUES(? +, ?);") or die $dbh->errstr; $sth->execute($upc, $item_id); $rows = $sth->rows; ($rows == 0) ? $true : $rows; # from DBI pod (using variable instea +d of "0E0") print "rows is $rows\n" if ($DEBUG); if ($rows < 1) { print "Didn't update so trying to insert...\n" if ($DEBUG); $sth2->execute($item_id, $upc); $rows = $sth2->rows; ($rows == 0) ? $true : $rows; print "rows is $rows\n" if ($DEBUG); if ($rows < 1) { print "Didn't insert so giving up...\n" if ($DEBUG); }else{ $status = "Inserted"; } }else{ $status = "Updated"; } return $status; }
    Comments, suggests?

    Matthew Musgrove
    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?

      I believe ($rows == 0) ? $true : $rows; doesn't do anything useful. Or am I wrong?

        Yeah, I think I made some heavy modifications and those were just left-overs that got missed. I can't say for sure because you replied to a ten year old post.

        doesn't do anything useful. Or am I wrong?

        It doesn't do anything useful -- even ten years later :)

Re: Database Update or Insert
by asdfgroup (Beadle) on Apr 26, 2002 at 18:53 UTC
    Also mysql will return count as '0E0', so you check (!'0E0') will be always false.

    it should looks like  unless ($sth->execute()+0) {....

      Perhaps someone should tell Jochen Wiedmann, Alligator Descartes, Gary Shea, Andreas Konig, and Tim Bunce. ;) Why? I got the original error checking code straight from the DBD::mysql pod. I was reading through it again this weekend and said "Hey! That's where I got it from!" :)
      And yes, I do talk to myself. And it's perfectly normal. Just ask my shrink. :D

      Matthew Musgrove
      Who says that programmers can't work in the Marketing Department?
      Or is that who says that Marketing people can't program?
        Pardon the newbie question - is there a DBD::Oracle pod, and if so, where might I find some example code for it...
Re: Database Update or Insert
by Mr. Muskrat (Canon) on Apr 26, 2002 at 20:01 UTC
    Okay, thanks for the comments. I'm still learning MySQL and the DBI module.

    Matthew Musgrove
    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?
Why not Replace?
by powerman (Friar) on Apr 27, 2002 at 11:55 UTC
    Hmm..
    Why not use REPLACE instead of UPDATE+INSERT?
    The only problem with REPLACE is when you UPDATE not all fields in table, but for your example REPLACE is good.
    $dbh->do("REPLACE INTO $table SET item_id=?, upc=?", undef, $item_id, $upc) or die $dbh->errstr;
      This won't work because of 2 resons :

      1) This will work only for key field but this is not clear based on example item_id is keyfield

      2) record can have additional fields. All this fields after replace will be empty

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: CUFP [id://162350]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (8)
As of 2024-03-28 23:59 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found