How to delete duplicate records with the same id except for one record from database table in Larave-CodePudding

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();

CodePudding user response：

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();

CodePudding user response：

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;