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?
2
-
Take a look at this related thread: [mysql duplicates with load data][1] [1]: stackoverflow.com/questions/1965001/…Oscar Gomez– Oscar Gomez2011-07-19 15:56:51 +00:00Commented Jul 19, 2011 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– Tim Specht2011-07-19 16:00:57 +00:00Commented Jul 19, 2011 at 16:00
Add a comment
|
1 Answer
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, ...)
7 Comments
Tim Specht
so how should the entries in the file look like? I guess it shouldn't be like that:
INSERT INTO myTable (coloumn1,...) (value1,...)
Ike Walker
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: dev.mysql.com/doc/refman/5.1/en/load-data.html
Tim Specht
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 ';'Ike Walker
Does your text file contain every column form the table, in the exact same order they appear in the table definition?
Tim Specht
yea, the first row gets properly inserted, the other strangely not...phpmyadmin tells me that only 1 row gets inserted too
|