CODEWITHSUNDEEP
phpmysql
Majinbibo
Majinbibo

Reputation: 159

I want to update data in table by batch

I have this temp table that holds the data to be modified in table1, to update table1 I use the query:

UPDATE table1 pr
INNER JOIN tmpTable tmp
ON (pr.product_id = tmp.product_id)
SET pr.isactive = tmp.isactive

But since tmpTable holds huge amount of data to update, my query sometimes ending up to 'timeout'. So my question is, what's basically the simplest way to update my data by batch? say, by 10K.

Thanks in advance!

Upvotes: 0

Views: 86

Answers (3)

atxdba
atxdba

Reputation: 5216

You tagged this in PHP so I'm assuming you're willing to do some work with in there and not just in a single query. Run the query multiple times. Something like

for($i<$minId; $i<maxId;$i+=10000){

  $db->query("UPDATE table1 pr
  INNER JOIN tmpTable tmp
  ON (pr.product_id = tmp.product_id)
  SET pr.isactive = tmp.isactive where isactive between $i and $i+10000");

}

If you're running MyISAM you risk things ending up in a partially completed state this way or yours. If your running innodb and want to maintain the all or nothing aspecs of a transaction you'll have to wrap that loop in a begin/commit. But, then you'll have the deal with the fallout of having potentially overlly large transactions.

If you can provide more details on your specifics I can go deeper down that route.

Upvotes: 1

user1135379
user1135379

Reputation: 136

You can limit the number of records by using a primary or identity key in your temp table and a WHERE clause in your UPDATE statement. For example:

UPDATE table1 pr
INNER JOIN tmpTable tmp
ON (pr.product_id = tmp.product_id)
SET pr.isactive = tmp.isactive ***WHERE tmp.ID BETWEEN 1 and 10000***

Hope this helps.

Upvotes: 1

Adrian Cornish
Adrian Cornish

Reputation: 23858

Use a WHERE clause to limit your data - how to format the where is impossible to answer with the information you currently provide.

Upvotes: 0

Related Questions