How to merge rows in MySQL while redirecting all foreign keys

February 2021

So let’s say you have some rows that are duplicates which you’d like to merge. But there might also be other tables referencing the duplicates. If you build something for merging rows, how can you be sure you don’t forget any references, as your app grows?

The best solution is to write a unit test that checks the number of references to the id column and compares this to how many columns are referenced by your code.

Here is an example in Kotlin:

 * Test that all references to topicIds are handled
 * */
fun testMergeTopics() {
    // Counts the number of references to the column
    val resultSet = context().database().connection().prepareStatement("""SELECT COUNT(*)
AND REFERENCED_COLUMN_NAME = 'id';""").executeQuery()
    val resultSetSize = resultSet.getInt("COUNT(*)")

    // Generate a dummy MergeTopicsQuery to see how many references will be redirected
    val mergeTopics = MergeTopics(1, 2)
    val create = DSL.using(context().database().connection(), MARIADB)
    val updateList = mergeTopics.getUpdateList(create = create)

    // There should be as many updates as there are references to the column
    assertEquals(resultSetSize, updateList.size)

and with getUpdateList being

fun getUpdateList(create: DSLContext): List<RowCountQuery> {
    return listOf(
        create.update(TOPICTAGS).set(TOPICTAGS.TOPICID, toKeepId)
        // ...

That’s more or less the general approach, how exactly you’re going to implement it then depends on the language & framework that you’re using. I hope I could give you some pointers on how a merging of duplicate rows could be performed safely with MySQL.

Dear Devs: You can help Ukraine🇺🇦. I opted for (a) this message and (b) a geo targeted message to Russians coming to this page. If you have a blog, you could do something similar, or you can link to a donations page. If you don't have one, you could think about launching a page with uncensored news and spread it on Russian forums or even Google Review. Or hack some russian servers. Get creative. #StandWithUkraine 🇺🇦
Dear russians🇷🇺. I am a peace loving person from Switzerland🇨🇭. It is without a doubt in my mind, that your president, Vladimir Putin, has started a war that causes death and suffering for Ukrainians🇺🇦 and Russians🇷🇺. Your media is full of lies. Lies about the casualties, about the intentions, about the "Nazi Regime" in Ukraine. Please help to mobilize your people against your leader. I know it's dangerous for you, but it must be done!