Mysql – Merge and then Delete duplicate entries

MySQL

I have mySQL database with some duplicate entries. They have the same field – phone. But they also had fields which differs. At example I have two entries with same phone, but first entry has rating filed = default_value and second entry has rating field = 5.
So I must merge this entries and only then delete duplicates…

More common example:

    entry1.phone==123
entry1.phone==etry2.phone
    entry1.rating!=entry2.phone
    entry1.rating==default_value(0)
    entry2.rating==5
    merge
    entry1.phone==123
    entry1.rating==5
    entry2 is deleted

Best Answer

I don't think you can do this in SQL efficiently. One slow way to do it is something like:

CREATE TEMPORARY TABLE tmp_table (...);
INSERT INTO tmp_table SELECT phone, max(rating) FROM table GROUP BY phone;
TRUNCATE table;
INSERT INTO table SELECT * FROM tmp_table;

A better way would be a stored procedure or an external script. Select all rows from the table ordered by phone and do the grouping/merging/deleting manually (iterate over the results, compare to the phone value from the previous row, if it's different you have a new group, etc.). Writing stored procedures in MySQL is painful though, so I'm not going to write the code for you. :)

Related Solutions

Mysql – Common MySQL fields and their appropriate data types

Someone's going to post a much better answer than this, but just wanted to make the point that personally I would never store a phone number in any kind of integer field, mainly because:

You don't need to do any kind of arithmetic with it, and
Sooner or later someone's going to try to (do something like) put brackets around their area code.

In general though, I seem to almost exclusively use:

INT(11) for anything that is either an ID or references another ID
DATETIME for time stamps
VARCHAR(255) for anything guaranteed to be under 255 characters (page titles, names, etc)
TEXT for pretty much everything else.

Of course there are exceptions, but I find that covers most eventualities.

Mysql – “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
Inserting a NULL into a column with a NOT NULL constraint.
Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

A new auto-increment ID is allocated.
Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
Triggers that fire on DELETE are executed unnecessarily.
Side effects are propagated to replicas too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

Best Answer

Related Solutions

Mysql – Common MySQL fields and their appropriate data types

Mysql – “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

Related Topic