stwilson's avatar

DB updateOrInsert , upon reaching index constraint won't update

Using Laravel 5.4, I'm having a problem with updateOrInsert error when it encounters a unique index constraint with duplicate data. I'm working through a Laracast lesson Incremental APIs: Level 5: Seeders Reloaded.

Using MySQL the loop, shown below, executes until it encounters the unique index constraint on the pivot table, lesson_tag:

$lessonIds = Lesson::pluck('id')->toArray();
$tagIds = Tags::pluck('id')->toArray();

foreach(range( 1, count($lessonIds)) as $index) {

    shuffle($tagIds);
    shuffle($lessonIds);

    DB::table('lesson_tag')->updateOrInsert([
        'lesson_id' => $lessonIds[0],
        'tag_id' => $tagIds[0]
    ]);

}

Here is the error after it successfully writes any number of records before it encounters a duplicate:

c:\wamp\www\api-lessons>php artisan db:seed
Seeding: TagsTableSeeder

  [Illuminate\Database\QueryException]
  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 'where (`lesson_id` = ? and `tag_id` = ?) limit 1' at line 1 (SQL: update `lesson_tag` set  where (`lesson_id` = 17 and `t
  ag_id` = 7) limit 1)

  [PDOException]
  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 'where (`lesson_id` = ? and `tag_id` = ?) limit 1' at line 1

Here is the error using SQLite instead of MySQL:

c:\wamp\www\api-lessons>php artisan db:seed
Seeding: TagsTableSeeder


  [Illuminate\Database\QueryException]
  SQLSTATE[HY000]: General error: 1 near "where": syntax error (SQL: update "lesson_tag" set  where ("lesson_id" = 15 and "tag_id" = 4))



  [PDOException]
  SQLSTATE[HY000]: General error: 1 near "where": syntax error

Baffled, I am using SQLite specific language in lieu of updateOrInsert like this:

DB::statement('INSERT OR IGNORE INTO lesson_tag ( lesson_id, tag_id ) VALUES( '. $lessonIds[0] . ', ' . $tagIds[0] . ' )');
0 likes
0 replies

Please or to participate in this conversation.