Question 15990 in Laravel Question posted in
The official Laravel Documentation can be found here.

How to delete duplicate records with the same id except for one record from database table in Laravel?

I have five same records in my table with the same id. I need a query that will check if there are duplicate values in table and then delete all rows except one. I need that one row to remain intact. Here is example from database and my current query that deletes every record. Any help is appreciated.

NOTE: I don’t have id column in table, only google_maps_city_id

table geo_postal_global

country   city    google_maps_city_id
Germany   Berlin  ChIJAVkDPzdOqEcRcDteW0YgIQQ // for example I need that one to not be deleted
Germany   Berlin  ChIJAVkDPzdOqEcRcDteW0YgIQQ
Germany   Berlin  ChIJAVkDPzdOqEcRcDteW0YgIQQ
Germany   Berlin  ChIJAVkDPzdOqEcRcDteW0YgIQQ
Germany   Berlin  ChIJAVkDPzdOqEcRcDteW0YgIQQ

My query

$duplicates = DB::table('geo_postal_global')
    ->select('google_maps_city_id')
    ->where('google_maps_city_id', '!=', null)
    ->whereIn('google_maps_city_id', function ($q){
             $q->select('google_maps_city_id')
              ->from('geo_postal_global')
              ->groupBy('google_maps_city_id')
              ->havingRaw('COUNT(*) > 1');
        })->pluck('google_maps_city_id');

        $q = collect($duplicates);
        $t = json_decode($q);
        $m = array_unique($t);
        DB::table('geo_postal_global')->whereIn('google_maps_city_id', $m)->delete();
Tags:

2

Answers


  1. 0

    Here is a simple query using two ways 🙂

    Eloquent

    $geoCount = GeoPostalGlobal::where('google_maps_city_id', 'ChIJAVkDPzdOqEcRcDteW0YgIQQ')
    ->count();

return GeoPostalGlobal::where('google_maps_city_id', 'ChIJAVkDPzdOqEcRcDteW0YgIQQ')
    ->skip(1)
    ->take($geoCount - 1)
    ->delete();

    Query Builder

    $geoCount = DB::table('geo_postal_global')
    ->where('google_maps_city_id', 'ChIJAVkDPzdOqEcRcDteW0YgIQQ')
    ->count();

return DB::table('geo_postal_global')
    ->where('google_maps_city_id', 'ChIJAVkDPzdOqEcRcDteW0YgIQQ')
    ->skip(1)
    ->take($geoCount - 1)
    ->delete();
    Login or Signup to reply.
  2. 0

    I have tested this . I hope it helps you .

    CREATE TABLE temp LIKE products;
INSERT INTO temp 
    SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search