MySQL UPDATE multiple rows with results from API

Question

Say, I have a table with 4 columns. The first 2 columns are sourced from a feed. The columns value1 and value2 I need to update using results from API requests. The values from the API request should go into value1 and value2

+---------+------+-------+-------+
| prod_id | name | value1| value2|
+---------+------+-------+-------+
|   1105  | aat  |       |       |
|   1108  | bbv  |       |       |
|   1111  | ccq  |       |       |
|   1116  | dde  |       |       | 
|   1123  | iir  |       |       |
|   1125  | jjm  |       |       |
+---------+------+-------+-------+

For example the API request gives me these values:

1108, banana, apple
1116, cucumber, pineapple
1123, orange, melon

So in this example I would like to update these 3 records, but in my case I would have update around 1000-2000 records at a time.

+---------+------+--------+----------+
| prod_id | name | value1 | value2   |
+---------+------+--------+----------+
|   1105  | aat  |        |          |
|   1108  | bbv  |banana  |apple     |
|   1111  | ccq  |        |          |
|   1116  | dde  |cucumber|pineapple | 
|   1123  | iir  |orange  |melon     |
|   1125  | jjm  |        |          |
+---------+------+--------+----------+

How would I be able to update, say 2000 records using only one or a handful of queries? Would I need to use a temporary table or is there a way to do without?

I think you are FORCED to use an intermediary language, not directly from MySQL, like PHP, C#, Java, c++ etc. Query the Database->Query the API->Update the Database — Ricardo Ortega Magaña, Nov 22 '16 at 23:58

score 0 · Accepted Answer · edited May 23 '17 at 11:59

You can implement a kind of batch update using CASE as described in How to bulk update mysql data with one query?.

Alternatively if you control the database schema you can insert the results of the API calls in batch INSERT statements and then JOIN when you need to lookup a particular value.

Table A

+---------+------+
| prod_id | name |
+---------+------+
|   1105  | aat  |
|   1108  | bbv  |
|   1111  | ccq  |
|   1116  | dde  |
|   1123  | iir  |
|   1125  | jjm  |
+---------+------+

Table B

+---------+----------+-----------+
| prod_id |  value1  |   value2  |
+---------+----------+-----------+
|   1108  | banana   | apple     |
|   1116  | cucumber | pineapple |
|   1123  | orange   | melon     |
+---------+----------+-----------+

When results are retrieved from API calls they can be inserted in batch.

INSERT INTO B (prod_id, value1, value2) VALUES 
(1108, 'banana', 'apple'), 
(1116, 'cucumber', 'pineapple'), 
(1123, 'orange', 'melon')

Later if you need all values for prod_id 1108 for instance you can retrieve them whit a simple join of tables A and B.

SELECT A.prod_id, A.name, B.value1, B.value2
FROM A JOIN B ON A.prod_id = B.prod_id
WHERE A.prod_id = 1108

The [link](http://stackoverflow.com/questions/11664684/how-to-bulk-update-mysql-data-with-one-query) you provided was very helpful. For my purposes the easiest solution was the "ON DUPLICATE KEY UPDATE" one mentioned by user Yaroslav. — dean2020, Nov 23 '16 at 08:35

MySQL UPDATE multiple rows with results from API

1 Answers1