Well you should have errors--you're fetching from
$sth before you execute it, then clobbering it
inside your while loop. | [reply] |
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";
}
| [reply] |
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
| [reply] |
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.
| [reply]
[d/l]
[select] |