-1

I am trying to insert data from a text file (18.9GB large) that looks like this:

as8dyta89sd6892yhgeui2eg

asoidyaos8yd98t2y492g4n2

as8agfuigf98safg82b1hfdy

They are all a length of 32 characters. Currently I have a database named hashmasher and a table called combinations with columns named unhashed and sha256. Currently I have data stored in the unhashed columns. Looking like:

unhashed | sha256

data | (completely empty)

Now I am wondering, how I could insert the data into the existing columns aswell as only adding the data to the second column, so for example the above would become

unhashed | sha256

data | firstlineoftextfile

data | secondlineoftextfile

If I use LOAD DATA INFILE it will load it into NEW rows (that's what I've been told) and it will load it into the unhashed column aswell as the sha256 column.

TL;DR I want to insert data from a text file into the second column of pre-existing rows.

Dingo Rogus
  • 1
  • 1
  • possible duplicate of [Import CSV to Update only one column in table](http://stackoverflow.com/questions/10253605/import-csv-to-update-only-one-column-in-table) – Zippers Apr 09 '15 at 20:58

1 Answers1

0

Insert your data with LOAD DATA INFILE into a new table. It may be temporary, to speed thing up a bit. Use INSERT ... SELECT ... JOIN to merge two tables.

I understand it can take a few hours with 19G table.

Things are more complicated, since your original file contains one value per row. You may want to fix it up with sed/awk script so that there are two values per row, so that LOAD DATA INFILE works.

The other approach is to go on with sed/awk scripting, and convert your original file into a file with a bunch of UPDATE statements, and then pipe the result to MySQL.

Kostja
  • 1,607
  • 10
  • 17
  • Thanks for the help, I should be able to just create a new table with the sha256 column, fill it with the 20GB text file and then update my main table or? – Dingo Rogus Apr 09 '15 at 22:26
  • Not exactly an update -- unless you do REPLACE ... SELECT. I recommend piping the data into the third table, as the result. When you see it's OK, you can swap the original table with the result with RENAME TABLE (it takes a list of pairs and renames things atomically) – Kostja Apr 09 '15 at 22:27
  • Would you mind giving me an example of this inserting selecting and joining? My tablename is combinations and my database is hashmasher, if that's too much work you don't have to reply but thanks for the main instructions. – Dingo Rogus Apr 09 '15 at 22:29