This really has nothing to do with Perl or DBI. You can do it with a single SQL, barring minor SQL dialect variations (you don't mention what database you're using).

SELECT *
INTO sp_err
FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier
    WHERE sp.num = supplier.num
)
[download]

Update
The SELECT * INTO form is non-standard SQL that's supported only a few places (Sybase comes to mind). The equivalent in standard SQL, presuming sp and sp_err have the same columns, would be:

INSERT INTO sp_err
SELECT * FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier
    WHERE sp.num = supplier.num
)
[download]

that sql didn't move the rows i also tried $sql = "SELECT * INTO sp_err FROM sp WHERE NOT EXISTS ( SELECT * FROM sp, supplier WHERE sp.snum = supplier.snum)";

VSarkiss's sql statement without the 'INTO sp_err' clause should be selecting the rows you want. It's up to you to tweak it so that it is selecting those rows into the sp_err table. That depends on the database (which you haven't told us what it is). Maybe it's not exactly 'INTO sp_err', maybe it's 'INSERT INTO sp_err (select statement here)' (I'm betting on that). The select statement itself, though, should be correct.

thanks. your queries were correct. i found out i have to upgrade mysql in order to use subqueries

$sql = "delete from sp where (select snum from sp a, supplier b where 
+a.snum = b.snum AND a.snum = null)";
#if it has not_null then it would be a.snum = ''
[download]

well, i am looking for rows in table sp where sp.snum does not exist in table suppliers. after i find these rows i have to move them (insert select) into table sp_err, then delete these rows out of table sp.

INSERT INTO sp_err SELECT * FROM sp
WHERE spnum = $spnum && snum = $snum
[download]

i tried the code below, no errors, but it's also not moving rows from table sp to table sp_err

# begin fix loop

$sql = "SELECT * FROM sp WHERE NOT EXISTS (SELECT * FROM SP,SUPPLIER WHERE SUPPLIER.snum = SP.snum)";
$sth2 = $dbh->prepare($sql);
$sth2->execute or die "Error: $DBI::errstr\n";

$sth2->bind_col(1,\$spnum);
$sth2->bind_col(2,\$snum);
$sth2->bind_col(3,\$pnum);
$sth2->bind_col(4,\$qty);

while($sth->fetch){
$sql = "INSERT INTO sp_err SELECT * FROM sp WHERE spnum = $spnum && snum = $snum";
$sth = $dbh->prepare($sql);
$sth->execute or die "Error: $DBI::errstr\n";
print "deleted row";

}

Well you should have errors--you're fetching from $sth before you execute it, then clobbering it inside your while loop.

i think i've narrows the problem to this line
$sql = "SELECT * FROM sp WHERE NOT EXISTS (SELECT * FROM SP,SUPPLIER WHERE SUPPLIER.snum = SP.snum)";
it's not finding any results. it's supposed to find all rows in table sp where sp.snum does not exist in table supplier

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