0

I am wondering how to delete duplicate entries in the DB table, and keeping the new copies in the table. This is how my table looks like:

 $table->increments('id');
 $table->string('text');
 $table->string('type');
 $table->integer('external_id');
 $table->timestamps();

Since I am importing from other DB, and I would like to import all data once a day, means there will be some existing entries already in DB, and new entries as well. Since I need to keep only the entries that are new, I need to delete all old entries (duplicates and unique entries that are not new). Is there a way to do it with Eloquent in Laravel 5.1? I have tried this, but it is deleting everything in the table:

$deleteDuplicates = DB::table('questions')->select('external_id')->distinct()->delete();
Ludwig
  • 1,401
  • 13
  • 62
  • 125

1 Answers1

1

Try this

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

The query might look like this:

DELETE n1 FROM questions n1, questions n2 WHERE n1.id > n2.id AND n1.name = n2.name

Check Delete all Duplicate Rows except for One in MySQL?

Community
  • 1
  • 1
a45b
  • 489
  • 3
  • 19
  • That gives me the following error: QueryException in Connection.php line 651: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as `n1` from `questions` as `n1` inner join `questions` as `n2` on `n1`.`id` > `' at line 1 (SQL: delete `questions` as `n1` from `questions` as `n1` inner join `questions` as `n2` on `n1`.`id` > `n2`.`id` where `n1`.`external_id` = n2.external_id) – Ludwig Apr 01 '16 at 11:20
  • Check the above link. If eloquent didn't work use query builder where you can even put raw sql. But 1st check the SQL in your test db if that works or not. – a45b Apr 01 '16 at 11:28