List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 8 2002 8:02pm
Subject:Re: Simultaneous SELECT and UPDATE on the same table
View as plain text  
At 17:00 +0000 3/8/02, Corin Hartland-Swann wrote:
>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.

That's also your problem.  With mysql_use_result, it's *required*
that you completely finish the query before issuing another one.

I suppose you could write the IDs to a file, then read them back in
and use them to issue the UPDATE statements.

>
>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: cdhs@stripped        |
>\------------------------+-------------------------------------/

Thread
Simultaneous SELECT and UPDATE on the same tableCorin Hartland-Swann8 Mar
  • ETA for MySql having sub queries capabilitiesRolando Morales8 Mar
    • Re: ETA for MySql having sub queries capabilitiesMatt Wagner8 Mar
Re: Simultaneous SELECT and UPDATE on the same tablePaul DuBois8 Mar
  • RE: Simultaneous SELECT and UPDATE on the same tableRoger Baklund8 Mar
    • RE: Simultaneous SELECT and UPDATE on the same tablePaul DuBois9 Mar
      • RE: Simultaneous SELECT and UPDATE on the same tableCorin Hartland-Swann9 Mar