Mysql – Update column with different value for multiple rows using a csv file

MySQLupdate

I often find myself using Excel to create update queries.

When I need to update multiple rows with different values for a column, I simply use Excel to create the update queries (multiple).

Here's an example SQL Fiddle.

If I have a list like:

Productname,Text
OpenIDM,testing
OpenAM,Tested
OpenDJ,great

I want to update the table with these values, so I need to create queries like this:

update ForgeRock set text = 'testing' where productname = 'OpenIDM';
update ForgeRock set text = 'Tested' where productname = 'OpenAM';
update ForgeRock set text = 'great' where productname = 'OpenDJ';

The format I receive the "To Be Updated" string in could be anything: Excel, csv, text.

The number of rows involved varies, depending on the project. Sometimes it could be just a few thousand rows, but other times it could be tens of thousands. I never timed myself or the time it takes to execute, but the execution time isn't really the problem. I just want an easier way to achieve this.

By "easier", I mean I should be able to update the table as mentioned without the need of Excel or a temporary table.

For example: to load data from a file it is possible to use load data infile.

Best Answer

-- Create some working space:
CREATE TEMPORARY TABLE t (
    productname ...,
    text ...
);
-- copy all the .CSV into it:
LOAD DATA INFILE INTO t ... .csv ...;
-- do the Update en masse:
UPDATE ForgeRock
    JOIN t ON t.productname = ForgeRock.productname
    SET ForgeRock.text = t.text;

Assuming that nothing changes from run to run, exactly the same statements can be reused.

If you also need to insert "new" data, see IODKU.

Related Question