How to merge 2 Records in innoDB MySQL databases


Problem :

This is related to How to change ID in mysql

I also have checked other questions and none are quite like this one.

As we know, innodb has a feature. If I want to channge an id of a record for example, then all other table that point to the previous ID will magically be updated.

What about if I want to MERGE 2 records?

Say I have 2 businesses.

They have 2 ID.

I want to merge them into one. I also want to use innodb awesome feature to automatically change things.

I can't just change one of the id to the other ID. Or can I?

What would you do to merge 2 simmilar records in database?

Of course what actually goes into the combined record will be business decisions.

Basically I just do not want to pin point all the other table one by one. I think on update rule is there for a reason. Is there a way where I just change slaveID to masterID, keep ALL data in master the same, and then have the database itself (rather than my program) to repoint all tables that point to slaveID to point to masterID? of course, records for slaveID will be gone anyway.

For example, with normal mysql engine, you can change ID, and then you have to go through all table that points to the old ID to point the new ID instead. With innodb, that repointing is done by the database engine itself. Which is kind of cool. Why would anyone use non innodb engine anyway.

I want to do the same but for merging.



Solution :

Trying to set a records primary key to an already existing value will simply result in a key violation error. While this is simple on a first glance, it has a side effect: You can not use ON UPDATE CASCADE to merge two records - it will simply not work.

If you have the possibility to change the schema, you can use the old but good redirect-trick:

(Assuming your IDs are positive, maybe unsigend ints)

  • add a field redirect int not null default 0
  • Create a view:

.

CREATE VIEW tablename_view 
SELECT 
  -- repeat next line for every field apart from redirect
  IF(s.redirect>0,m.<fieldname>,s.<fieldname>
FROM tablename AS s
LEFT JOIN tablename AS m ON s.redirect=m.id
  • When you merge a record (slave) into another record (master) run UPDATE tablename SET redirect=<id_of_master> WHERE id=<id_of_slave>
  • Adapt your select queries to select from tablename_view instead of tablename
  • Create and use a maintenance script to weed out merger slaves

Mysql Tutorials

Mysql Howto..

How to 'alias' table and all columns at same time in mySQL?

How to import Data from CSV file to MySQL database without using LOAD DATA INFILE Syntax?

how to decrease queries in php/mysql array selection loop

How to count diffrent rows in MySQL ? (three columns as a whole)

how to insert hash into mysql using ruby

How to store upto 3GB any file in MySQL database using php??

How to `SELECT` and manufacture missing rows from previous values?

How to count all characters in all rows of a field in MySQL?

How to specify column type and length for a derived column in mysql

MySQL, how to JOIN many records to one

How should I structure my MySQL database?

how to reverse sort mysql data

Teach an M.B.A. the intricacies of Microsoft SQL Server (and how's it different from MySQL?) [closed]

How to get the number of rows that are not in the past

How to specify data type on create table as select in mysql?

how to get last updated column name from mysql [closed]

How to select rows with x but not y or z using HAVING in mysql

how to create database on mysql community server?

How to .Scan() a MySQL TIMESTAMP value into a time.Time variable?

How can I solve this mysql_num_rows error

How would I translate user-customizable advanced searches to SQL queries?

How to resolve error “#2006 - MySQL server has gone away”

How to stop too many requests in web applications?

How to select only records that have float numbers in column and not null (MYSQL)

How do I filter this MySQL query to only include items not rated by a specific user?

How do I populate timezones for Google Cloud SQL (from Windows)?

How to count date difference excluding weekend and holidays in MySQL

How should MySQL be installed in a Django application?

how to filter repeated rows using DATE in Mysql

Showing error if date out before date in