note
jarich
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.
<p>
I've rewritten this code with comments, so that you can get an idea of an alternative way:
<p>
<code>
#!/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;
}
</code>
<p>
I haven't tested any of that, there might be a few typos, but you should get the idea.
<p>
Good luck
<p>
jarich
427077
427077