Update many records, using a CSV file
From time to time you might be asked to update multiple records in a MySQL database, based on a CSV file. The CSV file might have a list of old values vs new values. Using this method will allow you to update all the records in one go.
Example
For this example, we have a products table called products_table, that has the following fields:
- id
- product
- product_code
- category
- quantity
The company has done a stock take, and would like to update all quantity fields to reflect the correct amount.
Method 1 (Simple)
Start by downloading the table as a .csv file, and open it in your spreadsheet editor (I prefer Libre Office Calc).
- Make the changes to the quantity fields against the static id numbers.
- Create a 2 column .csv file with the id numbers in the first column and the new quantities in the second column.
- Delete the top row with the two column names, you won't need them for the import.
- Access your database using phpMyAdmin.
- Select the table.
- Select the Import tab.
- Click the Choose file button and browse to your created .csv file.
- Select the Update data when duplicate keys found on import option.
- In the Column names field type id,quantity).
- Click Go.
Only the quantity field will be updated, and usefully where there is a match with the id field.
Method 2 (Advanced)
We will first create a temporary table that has an identical structure of the existing table. We will then import the data from the CSV file into the temporary table. We will then be able to join the two tables, using the unique id field. Having joined the two tables, we can overwrite all rows with the new values. Finally we should drop (delete) the temporary table.
Note that if you have access to the SQL server file system (ie. you own the MySQL server) then you can use the LOAD DATA INFILE command shown below. Otherwise you can simply import the data using the phpMyAdmin interface.
phpMyAdmin
For this example we will use phpMyAdmin, but you can of course run the scripts directly in MySQL if you have access to the server.
SQL Instructions
In phpMyAdmin run the following script, make changes to fit your own circumstances...
CREATE TEMPORARY TABLE temp_table LIKE products_table;LOAD DATA INFILE '/tmp/your_file.csv'
INTO TABLE temp_table
FIELDS TERMINATED BY ','
(id, product, product_code, category, quantity);
UPDATE products_table
INNER JOIN temp_table on temp_table.id = products_table.id
SET products_table.quantity = temp_table.quantity;
DROP TEMPORARY TABLE temp_table;
Thanks for visiting,
Steven