0

I have a CSV file which contains 1 filed. I want to import this filed values into a MariaDB table using the LOAD DATA INFILE command. This table has 9 columns with existing data (about 1 000 000 rows). The 9th one was added with alter table statement and contains the same default value. We need to overwrite these default values with the values of the CSV field.

When I run the following statement

LOAD DATA LOCAL INFILE '/tmp/file.csv'
REPLACE INTO TABLE myTable
CHARACTER SET utf8
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(column9);

I get this warning

Warning | 1062 | Duplicate entry '0' for key 'PRIMARY'

I seems MariaDB always try to put these values into the first column witch is the ID of the table.

Improve this question
New contributor
Hisab is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
4
  • 1
    Unclear. How many lines in the CSV? Is there only one field in the csv? If there is only one line, LOAD is not applicable. If there are many lines, are the "column9" values all the same? And which line goes with which row in the table?
    – Rick James
    May 22 at 16:06
  • 1
    Load the CSV into stage table designed specifically for loading. Copy data from stage table into main table. Drop stage table.
    – White Owl
    May 22 at 16:44
  • How many lines in the CSV? Exactly the same number as the table has (about 1 000 000). Is there only one field in the csv? Yes, one field. If there is only one line? No, there are exactly the same number of lines as the table has. The values of coumn9 in the table are the same value (default) but in the CSV there are the real values that we want to import to replace some default values in the table. the column name in the table and CSV is the same (column9).
    – Hisab
    May 22 at 18:04
  • The solution based on using stage table takes a while to finish because of join statement.
    – Hisab
    May 22 at 19:14

0

Reset to default

Your Answer

Hisab is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.