Generate UPDATE SQL statements from a csv file

Question

Example:

User ID, Last Name, First Name, Start Date, End Date
    001, Pudding  , Chocolate , 10/01/2018, 10/30/2018
    002, Cake     , Carrot    , 10/06/2018, 11/07/2018
    003, Pie      , Apple     , 10/03/2018, 11/01/2018

And let's say I already have existing users for ID's 001, 002, 003 and I would like to update their Last Name, First Name, Start Date, End Date. The table is much too large to use a plugin such as "MySQL for Excel" because I can't narrow down my search (the example above is simplified)

I had thought to save this as a .csv, open it up in a text editor and try to find/replace text and form an UPDATE statement.. but I am not even sure how to do this exactly.

I would like each row to be turned into this, going off of first row:

UPDATE desserts
SET lname = "Pudding", fname = "Chocolate", startdate = "10/01/2018", enddate = "10/30/2018"
WHERE user_id = 001;

I would have several thousands of these that I can then run in the database.

Are you working on Windows? What kind of editor do you use? Notepad++ ? All editors might have different regex implementations ... — powtac, Oct 30 '18 at 13:44
I would use LOAD DATA INFILE ( https://dev.mysql.com/doc/refman/8.0/en/load-data.html ) (which can read CSV files) to insert into a temporary table (https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html) and write a update query which joins on the real table and the temporary table on user id. — Raymond Nijland, Oct 30 '18 at 14:22
"much too large" implies that you should abandon Excel, CSV, Notpad++, etc, as soon as possible. Plus learn how to write some kind of application that uses MySQL for the datastore. — Rick James, Oct 31 '18 at 05:02
To build on Raymond's suggestion, use the "multi-table" `UPDATE` syntax. The final result is _one_ `CREATE TABLE`, _one_ `LOAD DATA` of your thousands of updates, and _one_ `UPDATE`. — Rick James, Oct 31 '18 at 05:04
If messing with regex is undesired, there are free helper sites like (http://www.convertcsv.com/csv-to-sql.htm) for generating SQL commands from uploaded CSV. — sonyisda1, Nov 06 '19 at 17:22

score 1 · Answer 1 · answered Oct 31 '18 at 10:19

Open Search > Search ... > Replace in Notepad++

Then use this as search

\s*(\d+)\s*,\s*([a-zA-Z]+)\s*,\s*([a-zA-Z]+)\s*,\s*(.+),\s*(.+)

and this as replace

UPDATE desserts SET lname = "$2", fname = "$3", startdate = "$4", enddate = "$5" WHERE user_id = $1;\n

Select "Regular Expression" and click the "Search and Replace".

Generate UPDATE SQL statements from a csv file

1 Answers1