MySQL – How to Import Updates to Unique Data from CSV

csvMySQL

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?

Best Answer

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.

Related Solutions

Postgresql – Behavior of PostgreSQL COPY FROM

The COPY FROM will not violate integrity of database.

When a constraint fails, an error is throw, and none of data are copied (it is rolled back).

This behavior is unchangeable. You can do a script in a programming language to help you in a copy.

Mysql – How should I migrate a large MySQL database to RDS

I just recently spent a lot of time trying to figure out a 15GB transition to RDS. Ended up finding a script on one of the amazon forums that I modified to my own uses and seems to work well. I'm not sure if you can do single transaction, but the dump itself is very quick compared to the actual transfer. I think 15GB only took me 12 minutes to dump, so even if it doesn't have single transaction option I don't think you'd have a very long span of time for inconsistencies to occur. I'm not sure if that's good enough for you, but I found the solution a lot more graceful than the flat file method.

#!/bin/bash

declare -a dbs=(dbname1 dbname2 dbname3 dbname4);

j=0
while [ $j -lt 4 ];
#4 is the number of dbs
do

echo "Dumping ${dbs[$j]} DB"
time mysqldump --order-by-primary --host=sourcehost --user=sourceuser --password=sourcepass `echo ${dbs[$j]}` > /tmp/`echo ${dbs[$j]}`.sql
echo "Adding optimizations to ${dbs[$j]}"
awk 'NR==1{$0="SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;\n"$0}1' /tmp/`echo ${dbs[$j]}`.sql >> /tmp/`echo ${dbs[$j]}`X.sql
mv /tmp/`echo ${dbs[$j]}`X.sql /tmp/`echo ${dbs[$j]}`.sql
echo "SET unique_checks=1; SET foreign_key_checks=1; COMMIT;" >> /tmp/`echo ${dbs[$j]}`.sql
echo "Copy ${dbs[$j]} into RDS"
time mysql --host=yourrds.rds.amazonaws.com --user=rdsuser --password=rdspassword `echo ${dbs[$j]}` < /tmp/`echo ${dbs[$j]}`.sql &

j=$(($j+1))
done

Best Answer

Related Solutions

Postgresql – Behavior of PostgreSQL COPY FROM

Mysql – How should I migrate a large MySQL database to RDS

Related Question