Delete duplicate records based on a single field

In this guide, we'll walk you through the process of removing duplicate records from a database table using Xano's no-code platform. We'll use a simple example to demonstrate how to identify and delete duplicate values based on a specific field, ensuring that only unique records remain in the table.

Step 1: Create a Data Model

First, let's create a data model in Xano to represent our example table. For this tutorial, we'll use a simple "Merchants" table with a single field called "Name."

1. In the Xano app, navigate to the "Data Models" section and click "Create Data Model."
2. Give your data model a name, such as "Merchants."
3. Add a field called "Name" with the data type set to "String."
4. Save the data model.

Step 2: Insert Sample Data

To better illustrate the process, let's insert some sample data into our "Merchants" table, including duplicate entries.

1. Navigate to the "Data" section in Xano.
2. Select the "Merchants" table you just created.
3. Click "Insert Record" and add the following values for the "Name" field:

• In-N-Out Burger
• Deli Delicious
• In-N-Out Burger
• Tasty Tacos
• In-N-Out Burger
• Deli Delicious

Now our table should have multiple duplicate entries for "In-N-Out Burger" and "Deli Delicious."

Step 3: Create an API Endpoint

Next, we'll create an API endpoint in Xano to handle the removal of duplicate records.

1. Navigate to the "APIs" section and click "Create API."
2. Give your API a name, such as "Remove Duplicates."
3. In the API editor, add the following code:

javascript
 // Query all merchant records
 let merchants = await query('Merchants').aggregate({
  groupBy: ['Name'],
  count: { field: 'Name' }
 });
 
 // Loop through the grouped records
 for (let item of merchants) {
  // Loop through the count (minus 1) to delete duplicates
  for (let i = 0; i < item.count - 1; i++) {
  await deleteRecord('Merchants', 'Name', item.Name);
  }
 }
 

Here's what the code does:

1. It queries all records from the "Merchants" table and aggregates them by the "Name" field, counting the occurrences of each unique name.
2. It then loops through the aggregated results (`merchants`).
3. For each unique name, it loops through the count of occurrences minus one (to keep one instance of the record).
4. Within this inner loop, it deletes the duplicate records from the "Merchants" table based on the "Name" field.

Step 4: Run the API

With the API endpoint set up, we can now execute it to remove the duplicate records from our "Merchants" table.

1. In the API editor, click the "Run" button.
2. Once the API has finished executing, navigate back to the "Data" section.
3. Select the "Merchants" table and verify that only unique records remain (one instance of each unique name).

Congratulations! You've successfully removed duplicate records from a database table using Xano's no-code platform.

Conclusion

Xano simplifies the process of working with data and building backend services, making it accessible to both non-technical users and developers alike. By following this guide, you've learned how to identify and delete duplicate records based on a specific field, ensuring data integrity and maintaining a clean database.