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
– Ricardo Ortega Magaña, Commented Nov 22, 2016 at 23:58

Community · Accepted Answer · 2017-05-23 11:59:56Z

0

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

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Nov 22, 2016 at 23:54

cjungel

3,7911 gold badge27 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dean2020 Over a year ago

The link you provided was very helpful. For my purposes the easiest solution was the "ON DUPLICATE KEY UPDATE" one mentioned by user Yaroslav.

Collectives™ on Stack Overflow

MySQL UPDATE multiple rows with results from API

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related