Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
So at work we are exclusively Mysql, and I want to take advantage of the "Insert...on duplicate key update" syntax with multiple value sets per statement. For example a raw sql statement might look like:
INSERT INTO sections (`campus`,`term`,`department`,`course`,`section`,`instructor`,`est_enrollment`,`act_enrollment`,`comment`,`b_delete`,`enabled`)
VALUES
(ABC - TRADITIONAL,2015A,DM,DM171,01,June, yost,1,1,2015/01/05 - 2015/01/23,0,'1'),
(ABC - TRADITIONAL,2015AB,DM,DM174,01,bernie, Abe,0,0,2015/01/05 - 2015/01/23,0,'1')
ON DUPLICATE KEY UPDATE `campus`=VALUES(`campus`),`term`=VALUES(`term`),`department`=VALUES(`department`),`course`=VALUES(`course`),`section`=VALUES(`section`),`instructor`=VALUES(`instructor`),`est_enrollment`=VALUES(`est_enrollment`),`act_enrollment`=VALUES(`act_enrollment`),`comment`=VALUES(`comment`),`b_delete`=VALUES(`b_delete`),`enabled`=VALUES(`enabled`)
There is a huge speed advantage to doing this when importing massive csv files. You can see the code I currently have working. The only problem with this approach is that there's no nice way to update relations on models when using the "insertOrUpdate()" function I created.
Is there a way to extend Eloquent in such a way that I could take advantage of the way relations currently work? Secondarily, is there a different approach I should be taking?
@toltech Hi there! I just came across your question (having just asked something pretty similar myself, here).
I wanted to first of all thank you because your code example helped me build something similar so I now have a working batch insert in my app using DB::statement(). However, like you, I was hoping for an Eloquent solution... Wondering if you had come across anything?
Again, many thanks for putting your question/example out there!
Hey @mpmurph - most welcome! Unfortunately I have not come across an eloquent solution beyond the one I show in the code. Basically for relations I just sort of manually handle them (i.e. if customer belongs to a school, and Customer has a school_id column, I just fill that id manually instead of having eloquent handle it.)
The only potential problem with having several sets of data in a insert on dup key update statement is that MYSQL has a limit to the number of placeholders you can have. I think its like 60k. SO, you have to make sure to limit the maximum number of placeholders you use. See my updated example of my final code here that takes the placeholder limit into consideration: https://gist.github.com/josh7weaver/fd80120810f6b3fc802d
Thanks, @toltech - good to know! I have persisted with manual solutions too (as per your code example) as I too could not find an Eloquent solution. Thanks, again!
@kobear, not the same thing. updateOrCreate runs two queries.
updateOrCreate and updateOrInsert is a similar method (a same way). "INSERT INTO ... ON DUPLICATE KEY ..." is a difference one.
updateOrCreate does not solve the problem. In fact, when I have concurrent jobs that might insert the same record and using updateOrCreate resulted in a SQL fatal error of duplicate key because both jobs checked and attempted to insert the same data. Using locks will cause latency. Best way for me was to use DB::raw and run a pure SQL query with INSERT...ON DUPLICATE KEY UPDATE
You try using eloquents insertOrIgnore() method. This worked for me.
See documentation: https://laravel.com/docs/8.x/queries#insert-statements
Please or to participate in this conversation.