mySQL + CSV file = grey hair.

September 9, 2008 9:28 AM

I have a mysql table, and i have a csv file. I need to update some(not all) rows in the mySQL DB using the data in the CSV file. Is there an easy way to do it? There are about 2700 rows that need to get updated. 3 of the cols. in the CSV can be used to find the correct rows in the DB (and yes, I am in fact a n00b) (everything is on a windows machine)
posted by photodegas to Computers & Internet (4 answers total)

Best answer: Import the CSV into the database as a new table.

Use that new table in a join in the update:

update mytable a join csv_table b on (a.col = b.col and a.col2 = b.col2 and a.col3 =b.col3)
set a.somecol = b.whatever ;

Note that this only updates rows where there is a join; we use three of the clumns in the CSV to determine if there is a join.
posted by orthogonality at 9:39 AM on September 9, 2008 [2 favorites]

This is best done using a script, and will need to be done individually on rows. You'll want to debug it first. If you're a n00b, you may not have the ability to do this yourself.

Essentially, though, you'll want to store all of your CSV values in variables, and do something like the following:

Say your 3 cols in the CSV used to find the right DB row are "Key1", "Key2", and "Key3" ... and the rest of your values are, let's just say "Address", "City", "State" and "Zip" as an example:

You want to go through each row of the CSV file with your script, and do:

UPDATE my_table SET Address=$Address, City=$City, State=$State, Zip=$Zip WHERE Key1=$Key1 AND Key2=$Key2 AND Key3=$Key3;

Now, the syntax won't be exact, because whatever language the script is written in may use different syntax for variables ($City or %City or just "City" or.. whatever), and you'll want to put quotes around your data... so City='$City' ... or City=$City.quote() .. or some such...

It's not that hard to do, really, but I don't know of a way to use a CSV in MySQL to UPDATE data -- only to insert. Well, there's a way, but it would involve a pretty complicated process of creating a temporary table and doing the updates "between tables" by joining them. To me, that's more complicated and scary than just writing a script.. but then again I write scripts all the time.

You might try getafreelancer.com or something to find someone to do the script cheaply... or you could message me if interested...
posted by twiggy at 9:42 AM on September 9, 2008

... orthoganality beat me to clicking submit on that alternative method. I guess as someone who uses mysql as a tool, and lives more in the world of scripting, my solution of writing a script to do it just makes more sense to me. to a more db-oriented person, maybe the best solution IS to do the second table thing...

Both will work.
posted by twiggy at 9:43 AM on September 9, 2008

Response by poster: Thank you, the post was just what I needed.

You guys/gals ROCK!
posted by photodegas at 10:48 AM on September 9, 2008