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.

Accessory

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;

Floury

I don't think, your solution will work. Please look close. I have the sku value and sale price in CSV file, these are the values, not the column name. This inner join would not work. – Accessory

You said _sku = meta_key, assumption is SKU in CSV = meta_key. If that is not true you need to specify relationship between csv and table in more readable way (that is use same column names for both csv and table) – Floury

You're missing the datatypes for the columns in the temporary table. – Stedt

Answer 2

If product_id is the unique column of that table, you can do that using CSV:

1. Have a CSV file of those you want to import with their unique ID. CSV file must be in same order of the table column, put all your columns and no column name

2. Then in phpMyAdmin, go to the table of database, click import

3. Select CSV in the drop-down of Format field

4. Make sure "Update data when duplicate keys found on import (add ON DUPLICATE KEY UPDATE)" is checked.

Pincer

Answer 3

You can import the new data into another table (table2). Then update your primary table (table1) using a update with a sub-select:

UPDATE table1 t1 set 
  sale_price = (select meta_value from table2 t2 where t2.post_id = t1.product_id)
WHERE
  (select count(*) from table2 t2 where t1.product_id = t2.post_id) > 0

This is obviously a simplification and you will most likely need to constrain your query a little further.

Make sure to backup your full database before attempting. I recommend you work on a non-production database until the process works flawlessly.

Croesus

Please have a look hte sale_price is not column name. It is the value for meta_key column. – Accessory

The query is just an example – Croesus

Answer 4

It seems to me that rAndom69's answer does not work on postgresql 12 but the join with the WHERE work:

UPDATE tableA
SET fieldToPopulateInTableA = temp_update_table.fieldPopulated
FROM temp_update_table 
WHERE tableA.correspondingField = temp_update_table.correspondingField

Donata