5

I have data in a MySQL table with a unique key. I want to import more recent data that is stored in a CSV at the moment. I would like it to overwrite the old data if the key already exists, or create a new row if the key does not exist. Does anyone know how to this in MySQL?

Thank you for your help!

Jeff

Jeff Erickson
  • 3,783
  • 8
  • 36
  • 43

3 Answers3

1

Use INSERT ... ON DUPLICATE KEY UPDATE.

INSERT INTO table (column) VALUES ('value') ON DUPLICATE KEY UPDATE column='value'
ceejayoz
  • 176,543
  • 40
  • 303
  • 368
1

use INSERT ... ON DUPLICATE KEY UPDATE

bensiu
  • 24,660
  • 56
  • 77
  • 117
0

I was looking for the answer to the originator's exact question here I can see by his last comment/question he was looking for something more. It stimulated the following solution.

Nestling another shell environment (e.g. MYSQL) inside a script or batch file brings a lot of headaches switching syntaxes. I tend to look for solutions that operate within one shell to cut down on those complications. I have found this command string:

mysqlimport --fields-terminated-by=, --ignore-lines=1 --local -uMYSQL_ACCT -pACCT_PWD YOUR_DB_NAME /PATH_TO/YOUR_TABLE_NAME.csv

I got this idea from Jausion's comment at MySQL 5.0 RefMan :: 4.5.5 mysqlimport w/Jausions Comment In a nutshell, you may import to a database table in a csv format by simply naming the csv file after the table and append the .csv extension. You may append to the table and even overwrite rows.

Here is a real life csv file content of one of my operations. I like to make human readable csv files that include the column headers in the first line, hence the --ignore-lines=1 option.

id,TdlsImgVnum,SnapDate,TdlsImgDesc,ImageAvbl

,12.0.3.171-090915-1,09/09/2015,Enhanced CHI,Y

NOTICE the comma is the first char, making the first field value "NULL".

Here is the linux bash command that created the second line:

echo null,"$LISTITEM","$IMG_DATE","$COMMENTS","$AVBL" | tee -a YOUR_TABLE_NAME.csv

What is important to know here is that the null field for the primary key id field allows mysql auto-increment to be applied and then just adds a new row to your table. Sorry, can't recall if I read this somewhere or learned it the hard way:)

So, Viola!, conversely and of MORE importance to this question is, you may OVERWRITE a whole row of data by supplying the primary key of the row in question.

I am just in the throes of designing a new table to fulfill exactly these requirements with the overwrite operation but, as I alluded to, I already use the NULL append-a-row auto-increment option.

Joseph Camaioni
  • 61
  • 1
  • 6
  • Completed the application and need to add what I have learned. When overwriting a row (specifying the primary key row number), the mysqlimport command needs the --replace option! – Joseph Camaioni Oct 30 '15 at 12:26