mysql - Query execution was interrupted, error #1317

Question

What I have is a table with a bunch of products (books, in this case). My point-of-sale system generates me a report that has the ISBN (unique product number) and perpetual sales.

I basically need to do an update that matches the ISBN from one table with the ISBN from the other and then add the sales from the one table to the other.

This needs to be done for about 30,000 products.

Here is the SQL statement that I am using:

UPDATE `inventory`,`sales` 
   SET `inventory`.`numbersold` = `sales`.`numbersold` 
 WHERE `inventory`.`isbn` = `sales`.`isbn`;

I am getting MySQL Error:

#1317 SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted

I am using phpMyAdmin provided by GoDaddy.com

Answer 1

I've probably come to this a bit late, but... It certainly looks like the query is being interrupted by an execution time limit. There may be no easy way around this, but here's a couple of ideas:

Make sure that inventory.isbn and sales.isbn are indexed. If they aren't, adding an index will reduce your execution time dramatically.

if that doesn't work, break the query down into blocks and run it several times:

UPDATE `inventory`,`sales` 
  SET `inventory`.`numbersold` = `sales`.`numbersold` 
WHERE `inventory`.`isbn` = `sales`.`isbn`
  AND substring(`inventory`.sales`,1,1) = '1';

The AND clause restricts the search to ISBNs starting with the digit 1. Run the query for each digit from '0' to '9'. For ISBNs you might find selecting on the last character gives better results. Use substring(inventory.sales,-1)`