How to Remove Duplicate Rows in Laravel

How to Remove Duplicate Rows in Laravel

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How to Remove Duplicate Rows in Laravel

Deleting duplicate rows based on a specific column, such as a mobile number, is a common data hygiene task. In MySQL, you might instinctively reach for DELETE FROM table WHERE col IN (SELECT col ...) or use window functions like ROW_NUMBER(). While these native SQL approaches work well in raw queries, translating them directly into Eloquent can sometimes lead to unexpected behavior regarding transaction safety or column ambiguity.

The most robust and idiomatic approach within the Laravel ecosystem is leveraging database transactions combined with a subquery that identifies the duplicates. You must ensure you are only deleting rows where the primary key or a unique identifier differs from the one you intend to keep. If your table has a standard auto-incrementing id column, this becomes straightforward: you keep the row with the lowest ID and delete the others for that specific mobile number.

Here is how you can structure this operation safely using Eloquent's query builder within a transaction block. This prevents partial updates if an error occurs mid-operation.

use Illuminate\Support\Facades\DB;

DB::transaction(function () {
    // First, identify the duplicates to determine which rows to delete.
    // We group by mobile_number and find groups where the count exceeds 1.
    $duplicates = DB::table('users')
        ->where('mobile_number', '!=', null) // Ensure we don't delete valid NULLs if that's a concern
        ->select('mobile_number')
        ->groupBy('mobile_number')
        ->havingRaw('count(*) > 1')
        ->get();

    foreach ($duplicates as $group) {
        // Find the minimum ID for this mobile number to keep.
        $minId = DB::table('users')
            ->where('mobile_number', $group->mobile_number)
            ->orderBy('id')
            ->first()->id;

        // Delete all rows with this mobile number that do NOT have the minimum ID.
        DB::table('users')->whereIn('id', function ($query) use ($minId) {
            $query->select('id')
                   ->from('users')
                   ->where('mobile_number', '=', $group->mobile_number)
                   ->where('id', '!=', $minId);
        })->delete();
    }
});

The logic here relies on finding the "anchor" row for each group of duplicates. By selecting the row with the smallest id within a transactional scope, you guarantee data consistency. If your business logic dictates that the most recently updated record should be kept instead, simply change the ordering to orderBy('updated_at', 'desc').

A critical pitfall to avoid is attempting to delete duplicates in a single pass without identifying specific IDs first. Running a bulk delete against a subquery directly in Eloquent often results in ambiguity errors because Laravel expects column names that are not grouped or aliased correctly within the context of an whereIn clause. Breaking it down into a loop that identifies the survivor ID for each group ensures the id column is always unambiguous to the database engine.

Another consideration involves performance on large datasets. If you have millions of rows, iterating through groups in PHP might hit memory limits. In such cases, consider executing the entire logic as a raw SQL query within the transaction block using DB::statement(). This allows the database engine to handle the set operations efficiently without loading intermediate result sets into PHP memory.

For instance, if you are comfortable with raw SQL inside Laravel, you can execute a single statement that deletes rows where the ID is not in the subquery selecting the minimum ID per group:

DB::statement("
    DELETE FROM users u
    WHERE NOT EXISTS (
        SELECT 1 
        FROM users u2 
        WHERE u2.mobile_number = u.mobile_number 
        AND u2.id = (
            SELECT MIN(u3.id) 
            FROM users u3 
            WHERE u3.mobile_number = u.mobile_number
        )
    )
");

This approach is declarative and efficient, pushing the complexity entirely to the SQL engine. However, it sacrifices some of the type safety and readability that Eloquent provides. Choose the method based on your team's preference for raw power versus object-oriented clarity.

If you require further assistance with complex data manipulation patterns or need a specialized solution for high-volume migrations, visiting https://laravelcompany.com can provide additional context on enterprise-grade Laravel practices.

Our network