Laravel - 删除SQL数据库中的重复行

I am trying to delete rows in my SQL database that have the same norad_cat_id. Because the data in my database will update everyday, new rows with the same norad_cat_id will be added. What I want to do is to delete all the rows the have the same norad_cat_id and only leave the most recently added one. So far I have tried a few solutions from Stack Overflow (none of which worked):

1:

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

2:

$deleteDuplicates = DB::table('satellites as n1')
    ->join('satellites as n2', 'n1.norad_cat_id', '>', 'norad_cat_id')
    ->where('n1.norad_cat_id', '=', 'n2.norad_cat_id')
    ->delete();

My database name is satellite.

TL;DR: Delete rows that have the same norad_cat_id in my database

EDIT:

Here is my 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 as $attributes) {
        $attributes = array_change_key_case($attributes, CASE_LOWER);
        Satellite::create($attributes);
    }
    $deleteDuplicates = DB::table('satellites as n1') 
      ->join('satellites as n2', 'n1.created_at', '<', 'n2.created_at') 
        ->where('n1.created_at', '=', 'n2.created_at') ->delete();
    $api->get('ajaxauth/logout');
    return redirect('/');   
}

EDIT:

I think I need to give a clear explanation of what I am trying to achieve: My database will update automatically. What I want to be able to do is to create a row if the norad_cat_id does not already exist in the database. If it already exists, I want it to take the row with the same norad_cat_id, delete it and only leave the most recent row using the timestamps I have in my database. So that I have one of each norad_cat_id.

I was looking at this: https://laravel.com/docs/5.4/eloquent#deleting-models and https://laravel.com/docs/5.4/database#running-queries. Maybe I can use this?

EDIT 2: Can anyone shed some light on this code I wrote:

DB::select( DB::raw('DELETE n1 FROM satellites n1, satellites n2 WHERE n1.id < n2.id AND n1.norad_cat_id = n2.norad_cat_id'));

I looked at some answers and other questions and tried coming up with something.

After looking at some other answers I found the one that best works for me:

DELETE FROM satellites WHERE id NOT IN (SELECT * FROM (SELECT MAX(n.id) FROM satellites n GROUP BY n.norad_cat_id) x)

This deletes all rows with the same norad_cat_id but leaves one with the highest id.

Assuming that you are using timestamps:

$target_data = DB::table('satellites')->select('norad_cat_id')->where('norad_cat_id',$id_to_delete)->orderBy('created_at','DESC')->get();

$i = 0;
$len = count($target_data );

foreach($target_data as $data){
    if ($i != $len - 1) {
        $data->delete();
    }
    $i++;
}

You should try this :

$delSatellite = DB::table('satellites')->select('norad_cat_id')->orderBy('created_at','DESC')->get();

foreach($delSatellites as $delSatellite){
  DB::table('satellites')->where('id',$delSatellite->id)->delete();

}

Hope this work for you!!!

Try this one it will keep only the duplicate and non-duplicate id lastest id and

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

in response to OP comment :

got an error - SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'norad_cat_id' in on clause is ambiguous

Which Means you have to specify which table the column refers...

reference : Delete all Duplicate Rows except for One in MySQL?

EDIT

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