Hi there,
I have a medium-size table (3 million rows) with several pieces of data including an ID and a number (called id and num below). I have a program which builds a hash in perl from a data file of the 'new' numbers for each id, only some of which have changed (5-10%). To update the table I tried using the following code (paraphrased): -------------------------------------------------------------------------- $sth = $dbh->prepare("SELECT id,num FROM table", { 'mysql_use_result' => 1 }); $sth->execute; $sth->bind_columns(\$id, \$num); while ($sth->fetch) { $new_num = $hash{$id}; $dbh->do("UPDATE table SET num = $new_num WHERE id = $id") if $num != $new_num; } $sth->finish; -------------------------------------------------------------------------- This works for a varying number of rows (usually 5,000 to 10,000) before it silently exits the loop. I'm using MySQL 3.23.49a I was wondering if there is a problem with doing this in MySQL. Because of the size of the table and my script's already gargantuan memory footprint (it tops out 600 MB by the time it gets to this stage) I need to avoid storing the entire result in DBI, hence mysql_use_result. As an alternative I could mark all the id's that have changed and then perform the updates after I have closed the table, but I expected this way of doing it to work fine. I have run it through numerous times, and it always drops out of the loop at the same sort of interval (but often differs from run to run). Should this be working or should I find another way? If it is a bug then I will of course try to produce more information, but I just want to check I'm doing the right thing first. Many Thanks, Corin /------------------------+-------------------------------------\ | Corin Hartland-Swann | Tel: +44 (0) 20 7491 2000 | | Commerce Internet Ltd | Fax: +44 (0) 20 7491 2010 | | 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027 | | Gilbert Street | | | Mayfair | Web: http://www.commerce.uk.net/ | | London W1K 5HJ | E-Mail: [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