Feb 21, 2017
0
Level 16
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] . ' )');
Please or to participate in this conversation.