Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Import CSV to Update rows in table

Tags:

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

enter image description here

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:

enter image description here

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.

like image 367
Irfan Avatar asked Feb 01 '14 07:02

Irfan


People also ask

How do I update a CSV file in database?

CSV file must be in same order of the table column, put all your columns and no column name. Then in phpMyAdmin, go to the table of database, click import. Select CSV in the drop-down of Format field. Make sure "Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE)" is checked.

How do I update values in MySQL workbench?

UPDATE `table_name` is the command that tells MySQL to update the data in a table . SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query. Note, when setting the update values, strings data types must be in single quotes.


1 Answers

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; 
like image 130
rAndom69 Avatar answered Oct 19 '22 20:10

rAndom69