How to Deal with Duplicate Primary Keys on MySQL

Recently, I came across with a problem where I had to copy and insert some MySQL data from one database to another.
The issue regarded the same table structure in both databases, but with different data content in each. It was vital not to lose any information when importing the dump from one table to the other.
One of the most important details was that it was possible to have the same tuple repeated in both tables and I could not afford overwriting any content.

So, here follows the command used:

mysqldump -u user -p  --no-create-info --insert-ignore mydatabase mytable > file.dump

The --no-create-info makes sure no DROP or CREATE info are added, so your table is not deleted and recreated and you don't lose any information.

With the --insert-ignore parameter, the data is inserted with the INSERT IGNORE method. Using so, when trying to insert a tuple with an already existent  primary key it is simply ignored. The duplicated key tuple is discarded and the previous existent tuple remains.

Another useful command may be the  INSERT ... ON DUPLICATE KEY UPDATE, that also, as the name suggests, deals with duplicated keys. This clause updates the value of the previous existing key in case of conflicts, so it can insert the new tuple.

For example if "myvalue" is a primary key, you can set:

INSERT INTO mytable (myvalue) VALUES (10)
ON DUPLICATE KEY UPDATE myvalue=myvalue+1;

Comments

Post a Comment

Popular posts from this blog

Slope One

Image
Slope One is a simple and efficient type of recommender system. Introduced by Daniel Lemire and Anna Maclachlan in 2005, it involves a simpler idea than the majority of other collaborative filtering implementations. While these usually calculate the similarity between vectors of items using the cosine or the Pearson methods, the Slope One approach recommends items to users based on the average difference in preferences of items.  The main idea of the algorithm is to create a linear relation between items preferences such as the relation F(x) = x + b. The name "Slope One" cames from the fact that here the "x" is multiplied by "1". It basically calculates the difference between the ratings of items for each user (for every item the user has rated). Then, it creates and average difference (diff) for every pair of items. To make a prediction of the Item A for an User 1 for example, it would get the ratings that User 1 has given to other items and a...
Read more

Apache Mahout

Image
“Scalable machine learning library” Mahout is a solid Java framework in the Artificial Intelligence area. It is a machine learning project by the Apache Software Foundation that tries to build intelligent algorithms that learn from some data input. What is special about Mahout is that  it is a scalable library, prepared to deal with huge datasets. Its algorithms are built on top of the Apache Hadoop project and, so, they work with distributed computing. Mahout offers algorithms in three major  areas: Clustering, Categorization and Recommender Systems. This lats part was incoporated in April 4 th 2008, from the previous Taste Recommender System project. Mahout currently implements  a collaborative filtering engine that supports the user-based, item-based and Slope-one recommender systems. Other algorithms available in the package are  the k-means, fuzzy k-Means clustering, Canopy, Dirichlet and Mean-Shift. They also have The Naive Bayes, Complement...
Read more

Error when using smooth.spline

When trying to interpolate a series of data the cubic spline  is a great technique to be used. I choose to use the smooth.spline function, from the R stats package. > smooth.spline(data$x,  data$y ) Nevertheless, while running smooth.spline on a collection of datasets with different sizes I got the following error: Error in smooth.spline( data$x,  data$y),  :   'tol' must be strictly positive and finite After digging a little bit I discovered that the problem was that some datasets were really small and smooth.spline wasn't being able to compute anything. Hence, make sure your dataset is big enough before applying smooth.spline to it. > if(length(data$x) > 30) {  smooth.spline( data$x,  data$y)  } UPDATE:  A more generalized solution would be: > if(IQR(data$x) > 0) {  smooth.spline( data$x,  data$y)  }
Read more