There are a number of problems with your code. For starters your second statement could have problems if spnum or snum ever include non-numerical tables and other such. You're also clobbering $sth inside your while loop so that the second time through the conditional there's nothing to fetch.

I've rewritten this code with comments, so that you can get an idea of an alternative way:

#!/usr/bin/perl -w  
use strict;

use DBI;

# Connect to the database, passing in some very important 
# flags.
my $dbh->connect(... {RaiseError => 1,
                      ShowErrorStatement => 1,
                      AutoCommit => 0});

# etc

# Select out the entries we wish to move and delete.
# This could probably also be done with a selectall
my $set_to_move = $dbh->prepare(
        "SELECT a, b, c, d, snum
         FROM sp
         WHERE NOT EXISTS
            (SELECT 1 FROM sp, supplier
             WHERE supplier.snum = sp.snum)");

$set_to_move->execute();

# Prepare the statement for the insertion into sp_err 
# Note that in your code you were then overwriting the
# previous statement handle.
# By doing it out here, the code is a little more efficient
my $insert = $dbh->prepare(
         "INSERT INTO sp_err (a, b, c, d, snum) 
          VALUES (?, ?, ?, ?)");

# Prepare the delete statement as well.
my $delete = $dbh->prepare(
         "DELETE FROM sp where snum = ?");

while(my @values = $set_to_move->fetchrow_array()) {

     # Start a transaction.  Thus if we can't do both
     # operations, neither get done.
     $dbh->begin;

     $insert->execute(@values);

     # -1 isn't special here, it's just the index 
     # (in this case the last position) that snum is in.
     $delete->execute($values[-1]);

     # If both of those worked, then end the transaction
     $dbh->commit;
}
[download]

I haven't tested any of that, there might be a few typos, but you should get the idea.

Good luck

jarich

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.