#!/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;
}