Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

nested dbi queries question

by philosophia (Sexton)
on Feb 01, 2005 at 22:26 UTC ( #427077=perlquestion: print w/replies, xml ) Need Help??

philosophia has asked for the wisdom of the Perl Monks concerning the following question:

# am i going about this the right way? i want to
# select all rows from table sp where snum does not
# exist in table supplier. i want to then delete
# those rows in table sp

#i'm not sure if i have to make $sth inside of the while
#loop
#something like $sth2

$sql = "SELECT * FROM sp WHERE NOT EXISTS (SELECT * FROM SP,SUPPLIER W +HERE 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){ $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"; }

Edit by castaway - added code tags

Replies are listed 'Best First'.
Re: nested dbi queries question
by VSarkiss (Monsignor) on Feb 01, 2005 at 23:18 UTC

    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 )
    If you have to execute that from Perl, just put the whole thing in a $dbh->do(...).

    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 )
    As to "moving" rows: your original post didn't do that: it only inserted rows into sp_err. As to why it didn't work, see my reply elsewhere in this thread.

      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
Re: nested dbi queries question
by Grundle (Scribe) on Feb 01, 2005 at 22:43 UTC
    Why not just make an SQL DELETE statement? Something like the following?

    $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 = ''


    Is ther any reason why this wouldn't work?
      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.
Re: nested dbi queries question
by trammell (Priest) on Feb 01, 2005 at 22:58 UTC
    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?
      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
Re: nested dbi queries question
by jarich (Curate) on Feb 02, 2005 at 07:40 UTC
    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; }

    I haven't tested any of that, there might be a few typos, but you should get the idea.

    Good luck

    jarich

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://427077]
Approved by injunjoel
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (3)
As of 2022-05-29 12:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Do you prefer to work remotely?



    Results (101 votes). Check out past polls.

    Notices?