Import and overwrite existing data in MySQL

Question

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

Thank you for your help!

Jeff

Answer 1

Use INSERT ... ON DUPLICATE KEY UPDATE .

 INSERT INTO table (column) VALUES ('value') ON DUPLICATE KEY UPDATE column='value' 

Answer 2

use INSERT ... ON DUPLICATE KEY UPDATE

Answer 3

I was looking for the answer to the creator’s specific question, which I can see from his last comment / question that he was looking for for something more. This stimulated the following decision.

Holding another shell environment (e.g. MYSQL) inside a script or batch file leads to syntax for switching headaches. I try to look for solutions that work in one shell to reduce these complications. I found this command line:

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 a Jausion comment on MySQL 5.0 RefMan :: 4.5.5 mysqlimport w / Jausions Comment In a nutshell, you can import a database in csv format into a table by simply naming the csv file after the table and adding the .csv extension. You can add to the table and even overwrite rows.

Here are the contents of the CSV in real life of one of my operations. I like to make custom csv files that contain the column headers in the first row, so the -ignore-lines = 1 option.

identifier, TdlsImgVnum, SnapDate, TdlsImgDesc, ImageAvbl

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

COMMENT. the comma is the first char, which makes the first value of the "NULL" field.

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

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

It is important to know that a null field for the primary key id field allows you to apply auto-increment from mysql, and then just adds a new row to your table. Sorry, I can’t remember if I read it somewhere or learned it :)

So, Viola !, on the contrary, and MORE important for this question, you can TRANSFER the entire data row by providing the primary key of this row.

I just need to develop a new table to fulfill exactly these requirements using the rewrite operation, but, as I mentioned, I already use the option to automatically increase the NULL increment with the addition of a row.