0

I have a reasonably large MySQL database that is an import of CSV values from a public data repository. It has come to my attention lately that they occasionally update (ie. correct) data, usually from records published within the last few months. And, when I update my data, I only pull the last few months (not the entire database again).

I'm trying to figure out a way to import the recent data on a regular basis where it will OVERWRITE the current data with the new data if it conflicts. I have previously been doing this using the IGNORE command, to avoid conflicts, but that obviously keeps the original values.

I'd also prefer not to simply overwrite the entire database each month as it is a large file. So importing is a pain at over a gig of data.

So, is there a command like IGNORE that I can use that will have the opposite effect? One that will replace all columns that match unique keys (and thus cause a conflict) with the data from the NEWER import. Or a similar strategy?

1

The opposite for INSERT IGNORE is INSERT ON DUPLICATE KEY UPDATE

Refer to the chapter 14.2.5.3 of MySQL reference for further details.

| improve this answer | |
  • Thank you, I looked everywhere for that but could not track it down. I will confirm and accept your answer afterwards. – Keith Apr 24 '17 at 23:43
  • Out of curiosity, are you aware of any way to specify a certain key value to trigger the update? In other words, if sales are different, Update, otherwise ignore. – Keith Apr 24 '17 at 23:44
  • If any defined index of PRIMARY or UNIQUE type cause the duplication then defined update behaviour will be implied. – Kondybas Apr 24 '17 at 23:46
0

Some rows are new and need INSERTing? And some rows match on some PRIMARY or UNIQUE key, so they need UPDATEing? Or left alone if nothing changed?

If yes, the IODKU seems like the perfect command -- it will either INSERT or UPDATE. (And if nothing changes in the UPDATE path, then it is 'cheap'.)

Do you have an AUTO_INCREMENT key? (I hope not; that complicates things.)

If there is a performance problem, then load the CSV into a temp table, then use a minor variant on the 2 SQL statements given here.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.