php - Laravel - Deleting Duplicate Rows in SQL Database -

i trying delete rows in sql database have same norad_cat_id. because data in database update everyday, new rows same norad_cat_id added. want delete rows have same norad_cat_id , leave added one. far have tried few solutions stack overflow (none of worked):

1:

db::table('satellites')->select('norad_cat_id')->distinct()->delete(); 

2:

$deleteduplicates = db::table('satellites n1')     ->join('satellites n2', 'n1.norad_cat_id', '>', 'norad_cat_id')     ->where('n1.norad_cat_id', '=', 'n2.norad_cat_id')     ->delete(); 

my database name satellite.

tl;dr: delete rows have same norad_cat_id in database

edit:

here full function:

    public function displayer(){     $api = new client([     'base_uri' => 'https://www.space-track.org',     'cookies' => true,      ]); $api->post('ajaxauth/login', [       'form_params' => [          'identity' => '#',           'password' => '#',       ],     ]);     $response = $api->get('basicspacedata/query/class/satcat/orderby/intldes%20desc/limit/2/metadata/false');     $data = json_decode($response->getbody()->getcontents(), true);     foreach ($data $attributes) {         $attributes = array_change_key_case($attributes, case_lower);         satellite::create($attributes);     }     $deleteduplicates = db::table('satellites n1')        ->join('satellites n2', 'n1.created_at', '<', 'n2.created_at')          ->where('n1.created_at', '=', 'n2.created_at') ->delete();     $api->get('ajaxauth/logout');     return redirect('/');    } 

edit:

i think need give clear explanation of trying achieve: database update automatically. want able create row if norad_cat_id not exist in database. if exists, want take row same norad_cat_id, delete , leave recent row using timestamps have in database. have 1 of each norad_cat_id.

i looking @ this: https://laravel.com/docs/5.4/eloquent#deleting-models , https://laravel.com/docs/5.4/database#running-queries. maybe can use this?

edit 2: can shed light on code wrote:

db::select( db::raw('delete n1 satellites n1, satellites n2 n1.id < n2.id , n1.norad_cat_id = n2.norad_cat_id')); 

i looked @ answers , other questions , tried coming something.

try 1 keep duplicate , non-duplicate id lastest id ,

$deleteduplicates = db::table('satellites n1')    ->join('satellites n2', 'n1.norad_cat_id', '<', 'n2.norad_cat_id')      ->where('n1.norad_cat_id', '=', 'n2.norad_cat_id') ->delete(); 

in response op comment :

got error - sqlstate[23000]: integrity constraint violation: 1052 column 'norad_cat_id' in on clause ambiguous

which means have specify table column refers...

reference : delete duplicate rows except 1 in mysql?

edit

$ids_to_delete = array(1, 2, 3); db::table('satellites')->wherein('norad_cat_id', $ids_to_delete)->delete();