Navicat Blog

The majority of duplicate records fall into one of two categories: Duplicate Meaning and Non-unique Keys. The How to Spot and Delete Values with Duplicate Meaning in MySQL blog dealt with Duplicate Meaning; the follow-up addressed how to identify Non-unique Keys. That’s where two records in the same table have the same key, but may or may not have different values and meanings. Today’s blog will cover how to delete rows with duplicated data, but with different keys.

Unlike synchronization, which is a one-time process that brings the schema and data of two databases in sync, replication is a process that continuously (automatically) reproduces data between two databases (although schema updates are also possible). Replication may either be done asynchronously, so that a permanent connection between the two databases is not required, or during non-peak hours, when there is little traffic on the database server, for instance, during the late-night hours.

Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In today's blog, we'll use it to write a query to fetch a list of actors that appeared in movies released during a given year.

The majority of duplicate records fall into one of two categories: Duplicate Meaning and Non-unique Keys. The How to Spot and Delete Values with Duplicate Meaning in MySQL blog dealt with Duplicate Meaning; in today's follow-up, we'll address how to identify Non-unique Keys. That's where two records in the same table have the same key, but may or may not have different values and meanings.

One of the DBA's biggest annoyances is dealing with duplicate data. No matter how much we try to guard against it, duplicates always mange to find their way into our tables. Duplicate data is a big problem because it can affect application views (where each item is supposed to be unique), skew statistics, and, in severe cases, increase server overhead.