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

In reply to Re: nested dbi queries question by jarich
in thread nested dbi queries question by philosophia

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.