update in steps? is it possible ?

Expand Post »

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

Similar Threads

windows update doesn't work in Windows NT / 2000 / XP
how to update an access DB with vb.net? in ASP.NET
msn,messanger,update,ebay loggin problems,jus blank page in Web Browsers
Removal of "Home Search Assistant", "Search Extender", & "Shopping Wizard" in Viruses, Spyware and other Nasties
Windows Update keeps downloading the same Update in Windows 95 / 98 / Me

hbmarar

Reputation Points: 10

Solved Threads: 0

Junior Poster

Offline

187 posts
since Apr 2005

Permalink

Oct 13th, 2008

Re: update in steps? is it possible ?

try this code:

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;
}

Shanti C

Reputation Points: 137

Solved Threads: 162

Posting Virtuoso

Offline

1,641 posts
since Jul 2008

Permalink

Nov 26th, 2008

Re: update in steps? is it possible ?

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

Click to Expand / Collapse Quote originally posted by Shanti Chepuru ...

try this code:
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; }

hbmarar

Reputation Points: 10

Solved Threads: 0

Junior Poster

Offline

187 posts
since Apr 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Pulling related data from relational dB re: thread: Problems with a many-to-many inse

Next Thread in MySQL Forum Timeline: How to countdown to a date in MySQL

DaniWeb Message

Cancel Changes

User Name
Password