问题描述:

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?

网友答案:

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
相关阅读:
Top