Find solution
How does MySQL reindex a table?

MySQL: How to Selectively Update a Table from a CSV List?

  • MySQL: How Do I Selectively Update a Table from a CSV List? Here's an elementary problem I can't figure out. Let's say I have Table, which has 10K rows and 30 columns. I also have List, which is a .csv list with 1K rows and 2 columns. With List, one of those columns is an email address, and the other is the value "1", which is meant to go into a specific column on Table. All of the email addresses on List already exist in Table, but not all the addresses on Table are on List. My goal is, within Table, for all the addresses found on List, to set "Mangoes"=1 while leaving Oranges and Apples unchanged. So Table is like this:UserID | Email | Oranges | Mangoes | Apples | List is like this:Email | 1 And I'm trying to get Table, only for those cases where a given email is on List, to look like this:UserID | Email | Oranges | 1 | Apples | What would be the easiest way to do this? (I'm assuming this can be done either via PHP or by turning List into a Table...) Thanks! :)

  • Answer:

    Turn the list into a table. update table set column = 1 where email in ( select email from list_table) That's off the top of my head...syntax may be slightly off, but that's the general idea.

darth_tedious at Ask.Metafilter.Com Visit the source

Was this solution helpful to you?

Other answers

Yeah, turn the list into a table. If you don't have a tool that will do that some creative find-and-replace with a text editor will turn your CSV into a series of INSERT statements. To do it with a join rather than a recursive query as Jimbob uses then doSELECT table.emailcol, list.datacol, table.mangoescol FROM table JOIN list ON table.emailcol = list.emailcol;And if that successfully shows you all of the rows you want to change just rewrite it as an UPDATE statement.

XMLicious

If you haven't done it already, loading the CSV with http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html is the right way to turn it into a table.

beerbajay

(Ooops... before that query will show you the list of rows you want you'd have to add something like WHERE list.datacol=1)

XMLicious

> update table set column = 1 where email in ( select email from list_table) >SELECT table.emailcol, list.datacol, table.mangoescol FROM table JOIN list ON table.emailcol = list.emailcol; Thanks!

darth_tedious

I either produced, or just belatedly discovered, an error after using UPDATE `BigTable` SET `Mangoes`= '1' WHERE exists (SELECT temp_list.Mangoes FROM temp_list WHERE BigTable.Mangoes=temp_list.Mangoes); Specifically, I found a data row containing not data but column headings... and each was shifted over one column to the right, so that |Mangoes| _ _ _ _ _ _ Oranges Note that: a) I had uploaded the CSV in the format idtemp|Mangoes where "idtemp" is an incrementing number whose value is written into the cell (it's not autoincrementing); b) I included the column headers "idtemp|Mangoes" as a row in the file, as while as specifying them as column headers in the phpmyadmin csv-import interface; c) In creating the table temp.list, I believe I specified idtemp as Primary Key. Was the SQL code, or one of these three steps, incorrect... or did I just belatedly discover an already existing error?

darth_tedious

Not sure where your extra row came from, but I wanted to note that it looks to me like your update statement above will set the value to the string "1" rather than the integer 1.

XMLicious

>string "1" rather than the integer 1 Yes, that's true; thanks.

darth_tedious

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.