Laravel - Deleting Duplicate Rows in SQL Database

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.

can u try to run raw sql in Laravel ? such as DB::select( DB::raw("SELECT * FROM some_table WHERE some_col = '$someVariable'") ); Look how to delete duplicate rows on mysql; https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql — Mustafa Toker, Jul 28 '17 at 12:05
are you using timestamps? would be easy if you are using them — aaron0207, Jul 28 '17 at 12:07
@aaron0207, Yes I am using timestamps, so I guess you could use them — D. 777KLM, Jul 28 '17 at 12:08
what about your second method whats the error ? `$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();` — Arun pandian M, Jul 28 '17 at 12:21
@ArunpandianM - Tried your code, got an error - SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'norad_cat_id' in on clause is ambiguous — D. 777KLM, Jul 28 '17 at 12:36
@D.777KLM found the bug in your second query check my answer and let me know whether its working or not — Arun pandian M, Jul 28 '17 at 12:42
@D.777KLM Than remove the sql-server tag please I have done it for you this time — GuidoG, Jul 28 '17 at 13:36

Arun pandian M · Answer 1 · 2017-07-28T13:06:50.130

1

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

edited Jul 28 '17 at 13:06

answered Jul 28 '17 at 12:41

Arun pandian M

862
10
17

Thanks for your answer, but this still does not delete duplicate row. There is no more error now. – D. 777KLM Jul 28 '17 at 12:46
do you get any error ? can you create a `sqlfiddle` ? – Arun pandian M Jul 28 '17 at 12:49
I will post my full model now – D. 777KLM Jul 28 '17 at 12:50
remove `delete` and instead put `get` and check the output whether its returning something or check is that the `array` you want to `delete` – Arun pandian M Jul 28 '17 at 12:56
There is an output of the rows I want – D. 777KLM Jul 28 '17 at 13:00
your result is in single dimensional or multidimensional array ? – Arun pandian M Jul 28 '17 at 13:04
Single Dimensional – D. 777KLM Jul 28 '17 at 13:05
check my updated answer try it if possible else we will move it to chat for further conversation – Arun pandian M Jul 28 '17 at 13:07
No that did not work. Everything works except the delete old rows with the same `norad_cat_id` – D. 777KLM Jul 28 '17 at 13:12
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/150410/discussion-between-arun-pandian-m-and-d-777klm). – Arun pandian M Jul 28 '17 at 13:12

aaron0207 · Answer 2 · 2017-07-28T12:14:05.297

0

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++;
}

edited Jul 28 '17 at 12:14

answered Jul 28 '17 at 12:08

aaron0207

2,293
1
17
24

Wait, didn't read about multiple records with same id, thought there will be only 2 records with the same id EDIT: done! – aaron0207 Jul 28 '17 at 12:11
I tried this - got an Undefined variable. What do you mean by `$id_to_delete`? – D. 777KLM Jul 28 '17 at 12:37
Initialize it with the desired id to get clean of duplicates (you can use it after insert a new record with the desired id, for example) – aaron0207 Jul 28 '17 at 12:57
I tried adding `$norad_cat_id` but I still get the same error – D. 777KLM Jul 28 '17 at 13:02
When I said initialize I mean something like $norad_cat_id = 1; (supposing that this field is integer) – aaron0207 Jul 28 '17 at 15:39
Oh, Never thought about that. That fixed the error, but still does not delete the row. – D. 777KLM Jul 28 '17 at 16:16

score 0 · Accepted Answer · answered Jul 30 '17 at 22:38

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.

score -1 · Answer 4 · answered Jul 28 '17 at 12:11

-1

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!!!

answered Jul 28 '17 at 12:11

AddWeb Solution Pvt Ltd

21,025
5
26
57

1

This deletes all rows in my database. I might have been doing something wrong. I will see what I can do. – D. 777KLM Jul 28 '17 at 16:39

Laravel - Deleting Duplicate Rows in SQL Database

4 Answers4