mysql - Import CSV to Update rows in table

Question

There are approximately 26K products (posts) and each product has meta values like this:

The post_id column is the product id in db and the _sku (meta_key) is the unique id for each product.

I've received a new CSV file that updates all of the values (meta_value) for _sale_price (meta_key) of each product. The CSV file looks like:
SKU, Sale Price

How do I import this CSV to update only the _sale_price row based on the post_id (product id) & _sku value?

Output Example:

I know how to do this in PHP by looping through the CSV and selecting & executing an update for each single product but this seems inefficient.

Preferably with phpMyAdmin and by using LOAD DATA INFILE.

Answer 1

You can use temporary table to hold the update data and then run single update statement.

CREATE TEMPORARY TABLE temp_update_table (meta_key, meta_value)

LOAD DATA INFILE 'your_csv_pathname' 
INTO TABLE temp_update_table FIELDS TERMINATED BY ';' (meta_key, meta_value); 

UPDATE "table"
INNER JOIN temp_update_table on temp_update_table.meta_key = "table".meta_key
SET "table".meta_value = temp_update_table.meta_value;

DROP TEMPORARY TABLE temp_update_table;