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

Question

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.

Answer 1

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.