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

November 18, 2010 8:31 PM

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! :)

posted by darth_tedious to computers & internet (8 answers total)

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.
posted by Jimbob at 8:42 PM on November 18, 2010 [1 favorite]

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 do

SELECT 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.
posted by XMLicious at 9:27 PM on November 18, 2010 [1 favorite]

(Ooops... before that query will show you the list of rows you want you'd have to add something like WHERE list.datacol=1)
posted by XMLicious at 9:30 PM on November 18, 2010

> 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!
posted by darth_tedious at 1:03 AM on November 19, 2010

If you haven't done it already, loading the CSV with mysqlimport is the right way to turn it into a table.
posted by beerbajay at 4:04 AM on November 19, 2010 [1 favorite]

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?
posted by darth_tedious at 11:29 AM on November 19, 2010

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.
posted by XMLicious at 3:15 PM on November 19, 2010

>string "1" rather than the integer 1

Yes, that's true; thanks.
posted by darth_tedious at 5:33 PM on November 19, 2010