Re: nested dbi queries question

[trammell]

I see at least two problems. I think you want s/&&/AND/ in this SQL:

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

and your $sth variables will clash. How about $sth2?

[philosophia]

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

}

[trammell]

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

[philosophia]

here's my current code. same result still...

# begin fix loop

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

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

while($sth->fetch){

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

}

[philosophia]

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

[VSarkiss]

The problem is that your subselect is not correlated. In other words, you need to join sp in the main query to supplier in the subquery -- don't join the tables in the subquery. Specifically, the query should be:

SELECT *
FROM sp
WHERE NOT EXISTS (
    SELECT 1
    FROM supplier     -- note SP doesn't appear here!
    WHERE supplier.num = sp.num  -- sp is from main query
)

I wrote the same thing in my reply below, but didn't point it out specifically.