Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

how can I update 1000s of rows?

Posted on 2008-06-10
2
169 Views
Last Modified: 2013-12-13
hi experts,

using the function update1 I am trying to do the following:

start with: $limit1 =0;
use: update1($limit1);
echo $mymessage;

refresh the page ...
$limit1 +=50;
use: update1($limit1);
echo $mymessage;

refresh again until $limit1 > mysql_num_rows($query).


regards,
update1($limit1) {
.....
mysql_query("UPDATE  ... WHERE .... ORDER BY id LIMIT $limit1,50");
 
}

Open in new window

0
Comment
Question by:uk1900
2 Comments
 
LVL 31

Accepted Solution

by:
Frosty555 earned 500 total points
ID: 21756546
I'm guessing you want to do this so that you can do the updates in batches without the database hanging while it tries to update all the rows.

Your idea looks okay to me. The update command takes only a single number for the limit clause. Pass it 50, and UPDATE will update 50 rows, and then return. Simply devise your statement so that performing the update causes the record to no longer satisfy the WHERE clause, you can just call that over and over to keep updating more and more until zero rows are returned, in which case you're done.

Another thing to think about is that the UPDATE command will not be noticably slow until you are updating tens of thousands of records. Updating a mere 1000 records is nothing for MySql. Even on my desktop computer I can perform update statements on databases with hundreds of thousands of records, and it only takes 15-20 seconds or so to complete. So only in the most extreme of circumstances, where you have many tens of thousands of records, and users who are so impatent that they just can't sit and wait for it to finish, should you have to worry about this at all.
0
 

Author Comment

by:uk1900
ID: 21757075
I forced to use only 50 rows each time because the value to update depends on $limit1
In other words, the value to update in the first  50 rows, is not the same in the next 50 rows &.

That's why I need to repeat this while $limit1 < $max:
--------------------------
refresh the page ...
$limit1 +=50;
use: update1($limit1);
echo $mymessage;
--------------------------

Q: how can I refresh the page and echo a message each time?
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Complex SQL statement in VB.NET 7 31
MVC - procedural PHP 10 38
Combining Queries 7 28
What is this? I've never seen this... 2 9
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question