|
in reply to Re: nested dbi queries question
in thread nested dbi queries question
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";
}
|
Re^3: nested dbi queries question
by trammell (Priest) on Feb 01, 2005 at 23:40 UTC
|
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] |
Re^3: nested dbi queries question
by philosophia (Sexton) on Feb 01, 2005 at 23:36 UTC
|
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] |
|
