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
