MySQL

update in steps? is it possible ?

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2005

Posts: 183

Reputation: hbmarar is an unknown quantity at this point

Solved Threads: 0

hbmarar

Offline

Junior Poster

update in steps? is it possible ?

Oct 10th, 2008

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

•

Join Date: Jul 2008

Posts: 1,071

Reputation: Shanti Chepuru is on a distinguished road

Solved Threads: 98

Shanti Chepuru Shanti Chepuru is offline

Offline

Veteran Poster

Re: update in steps? is it possible ?

Oct 13th, 2008

try this code:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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;
}
$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;
}

Be intelligent, But Don't try to cheat.. Be innocent But Don't get cheated..

•

Join Date: Apr 2005

Posts: 183

Reputation: hbmarar is an unknown quantity at this point

Solved Threads: 0

hbmarar

Offline

Junior Poster

Re: update in steps? is it possible ?

Nov 26th, 2008

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

•

Originally Posted by Shanti Chepuru

try this code:
Help with Code Tags

php Syntax (Toggle Plain Text)
$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;
}
$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; }