3

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.

  • Have you considered exporting your Excel file to a CSV file, importing the CSV into a staging table in MySQL, then updating your target using a join to the staging table? – Andriy M Jun 3 '16 at 5:34
  • @AndriyM Yes, I have considered it, but am looking for an easier way if there is. Thanks for the suggestion though. – davejal Jun 3 '16 at 11:18
2
-- 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.