Your inner loop usage of $sth overwrites the result set of the outer loop.
Use a different variable.
Hannes Niedner wrote:
> I am having trouble with DBI. I wrote a little script that should update
> fields in one table (uid_test) based on values in another table (merge). I
> updates one row and then dies with:
>
> os prompt: blah blah
>
> 1 1011877 1000001
> Error during processing for table uid_test
>
> Error 19 (fetch() without execute())
>
> The selected values (primary_id, other_id) are both INT(12) and so are the
> fields of the target table (superceded_by, uid_new).
>
> Please have a look at the Perl code snippet below. As I said it works for
> the first row. BTW if there is pure sql code that would do the job, I would
> be delighted to learn about.
>
> Thanks Hannes
>
>
>
> ---------------------snippet---------------------------------------------
> #issue query
> $sth = $dbh->prepare ( "SELECT primary_id, other_id
> FROM merge
> ORDER BY other_id"
> ) or &bail_out ("Cannot prepare query from merge");
> $sth->execute () or &bail_out ("Cannot execute query from merge");
>
> while (@ary = $sth->fetchrow_array ()) {
> $counter++;
> my ($primary_id) = $ary[0];
> my ($other_id) = $ary[1];
> print "$counter\t$primary_id\t$other_id\n";
>
> #update the data in the target table
> $sth = $dbh->prepare ( "UPDATE $table_name
> SET superceded_by=$primary_id, status=\'1\',
> time=null
> WHERE uid_new=$other_id"
> ) or &bail_out ("Cannot prepare sql (UPDATE
> $table_name)!");
> $sth->execute () or &bail_out ("Cannot execute sql(UPDATE
> $table_name)!");
>
> }
>
> if (!defined($DBI::err)) {
> print "$counter sequences retrieved from table merge, cleaned out
> and successfully updated in table niedner.$table_name.\n";
> }else {&bail_out ("Error during processing for table $table_name\n");
> }
>
>
> #clean up
> $sth->finish () or &bail_out ("Cannot finish query from database");
> $dbh->disconnect () or &bail_out ("Cannot disconnect from database");
> exit (0)
> --------------------------snippet end------------------------------------
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--
Gerald L. Clark
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
