Re: nested dbi queries question

[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.

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;
}

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

Good luck

jarich