update in steps? is it possible ?

[hbmarar]

Hi ,

Wishes!

I am doing a iterative calculation and in the last iteration, i update few of my tables. What i am trying to do is , make this update more perfect.

Is it possible for me to do updates in small steps with using LIMIT. i do not find this anywhere mentioned and do not have a idea of my own. say i got 10000 records, i get this count and update in steps of 1000 each time as might be this would prevent from a update table to lock the entire table for long.

My table is innoDB type and i would appreciate some nice examples or pointers in this regard.

Harisaff

[Shanti C]

try this code:

$qry=mysql_query("SELECT * FROM yourtablename "); 
$num=mysql_num_rows($qry);
$start=0;
$len=1000;
$value=$_POST['field']
while($start<$num)
{
$f="update tablename set field='".$value."' LIMIT $start,$len";
$start=$start+1000;
$len=$len+1000;
}

[hbmarar]

Hi Shanti,

Thanks for the reply but I needed an advance logic for my script was in need of an alternative with temporary tables and the total record set to play with was 64 lakh records. Also, 5 iterations, with 5 temporary table and advanced 3 line formula computation gets it all challenging.

By the way, I had figures out for iterations and is well, now needs the final write back to master more replication sync.. working on that front.

Thanks again.

Regards

Harish

try this code:

$qry=mysql_query("SELECT * FROM yourtablename "); 
$num=mysql_num_rows($qry);
$start=0;
$len=1000;
$value=$_POST['field']
while($start<$num)
{
$f="update tablename set field='".$value."' LIMIT $start,$len";
$start=$start+1000;
$len=$len+1000;
}