0

Okay, so I have a MySQL table called entries which contains the columns name VARCHAR(255) NOT NULL and address VARCHAR(255)

The table has about a million sets of data.

For every set of data, name has a value e.g. "john" whilst address is NULL.

For example:

+------+---------+ | name | address | +------+---------+ | john | NULL | +------+---------+ | jake | NULL | +------+---------+ | zach | NULL | +------+---------+

I received a CSV file which contains names along with their corresponding address in the format of name:address.

Like I said, the entries table has nearly a million entries, so the csv file has about 800,000 lines.

I want to take each line in the csv, and insert the address where the name is the same which would be:

UPDATE `entries` SET `address` = <address from csv> WHERE `name` = <name from csv>;

I made a Python script to open the csv file reading it line by line. For each line, it would store the name and address in separate variables. It would then execute the query above, but it was taking too long to insert the data into the columns.

Is there anyway I could do this in MySQL, if so, what is the fastest way?

Thanks.

Improve this question

3 Answers 3

Reset to default
1

You can import the CSV file into a separate table using mysql LOAD DATA INFILE and then update the entries table using JOIN statement on the basis of similar column name.

E.g: update entries a inner join new_table b on a.name = b.name set a.address = b.address ;

Here new_table is imported from the CSV file..

Don't forget to add index on both tables for the name column so that it would be fast..

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Sorry, i used OUTFILE in place of Load data INFILE.. But its good that you got my point..
1

Create table1 and table2

LOAD DATA INFILE '/path/theFile1.csv' INTO TABLE table1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n'

Ditto for file2 into table2

Proceed

Improve this answer

4 Comments

Did you understand my question? This is not what I needed. Appreciate the attempt though.
I thought i did, starting with an import. Aman was doing an export with OUTFILE. Glad to help him
I didn't know that you meant for me to follow Arman's question after the LOAD DATA, but essentially I solved the problem by importing the name:address csv file into a new table, then using inner join to combine it with the old table.
Mainly trying to show you proper way to bring it in, then if you needed help would go from there (join). The options allow a great deal of flexibility as for row 1 with header column names, which columns to get (if you are skipping some), tab delimiters, \n or \n\r, etc
0

Well definitely using a batch query would be faster. You could use a for loop to scan through your CSV file and create a string that does a large batch query.

For example (pseudo code):

String Query ="UPDATE entries SET Value = ( CASE "; For (begin of file to end) Name = NameFromFile; Value = ValueFromFile; Query += "WHEN NameField = "; Query += Name + " THEN " +Value; End Query+= " )";

Of course you would need to convert those values to strings when concatenating. I wouldn't claim this is fastest but definitely faster.

Sorry for the poor formatting, I'm on my phone.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.