0

Hej,

I need to mass-update a table. Am I allowed to use mysql's "LOAD DATA INFILE" Statement together with "INSERT … ON DUPLICATE KEY UPDATE" statements to fulfill my task?

Tim Specht
  • 3,068
  • 4
  • 28
  • 46
  • Take a look at this related thread: [mysql duplicates with load data][1] [1]: http://stackoverflow.com/questions/1965001/mysql-duplicates-with-load-data-infile – Oscar Gomez Jul 19 '11 at 15:56
  • that's not quite what I'm looking for, I got the statement's ready to go, so I just want to query them. In addition wouldn't be there to much time going by when "SELECT ..."-ing from the temporary table? – Tim Specht Jul 19 '11 at 16:00

1 Answers1

3

Depending on your exact requirements, you may be able to accomplish this using the REPLACE option of LOAD DATA INFILE. From the manual:

  • If you specify REPLACE, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row. See Section 12.2.7, “REPLACE Syntax”.

Example:

LOAD DATA INFILE '/tmp/data.txt'
REPLACE INTO TABLE your_table
(column1, column2, ...)
Ike Walker
  • 64,401
  • 14
  • 110
  • 109
  • so how should the entries in the file look like? I guess it shouldn't be like that: `INSERT INTO myTable (coloumn1,...) (value1,...)` – Tim Specht Jul 19 '11 at 16:41
  • The default file format is tab-delimited, but you can override the field and line terminators to import a CSV or other format. Take a look at the doc page for LOAD DATA INFILE: http://dev.mysql.com/doc/refman/5.1/en/load-data.html – Ike Walker Jul 19 '11 at 17:48
  • I issued the following query but the database wont be filled...`LOAD DATA LOCAL INFILE "path/to/my/file/sqldump.txt" REPLACE INTO TABLE mytable FIELDS TERMINATED BY ',' LINES STARTING BY ';'` – Tim Specht Jul 19 '11 at 18:49
  • Does your text file contain every column form the table, in the exact same order they appear in the table definition? – Ike Walker Jul 19 '11 at 18:53
  • yea, the first row gets properly inserted, the other strangely not...phpmyadmin tells me that only 1 row gets inserted too – Tim Specht Jul 19 '11 at 18:53
  • Try specifying a line terminator, such as `LINES TERMINATED BY '\n'` – Ike Walker Jul 19 '11 at 18:59
  • thanks, now it seems to be completely solved...thanks for your help! – Tim Specht Jul 19 '11 at 19:07