Get "PHP 8 in a Nuthshell" (Now comes with PHP 8.3)
Amit Merchant

Amit Merchant

A blog on PHP, JavaScript, and more

Insert or update multiple records using upsert in Laravel 8.x

It would be useful sometimes when you’re not sure if you want to insert or update records and you’d let the system decide that. Laravel comes with one such feature that lets you do just that.

The upsert method

Laravel 8.x’s query builder comes packed with a method called upsert that will let you insert (multiple) rows that do not exist and update the rows that already exist with the new values.

So, for instance, let’s say, you have a table called books and it contains the following records right now.

name author quantity
J.K. Rowling Harry Potter 6
Ruskin Bond Angry River 10

As you can see, there are three fields for the books that I’ve shown here but in real life, there could be many. For the sack of simplicity, I’ve ignored the other fields.

Also, note that here, the name and author fields are unique for the books table.

Now, consider this scenario, where you’re adding more records into the table but there might be some records that are identical when it comes to name and author fields. In such cases, you’d want to update the quantity field.

This is where the upsert method can come in handy. Here’s how it works.

DB::table('books')->upsert([
    [
        'name' => 'J.K. Rowling', 
        'author' => 'Harry Potter', 
        'quantity' => 15
    ],
    [
        'name' => 'Cal Newport', 
        'author' => 'Deep Work', 
        'quantity' => 20
    ]
], ['name', 'author'], ['quantity']);

As you can see, the upsert method accepts three arguments.

  • The first argument consists of the values to insert or update.
  • The second argument lists the column(s) that uniquely identify records (name and author fields in our case) within the associated table.
  • The third and final argument is an array of columns that should be updated if a matching record already exists in the database. We want quantity to be updated in our case.

In our example, the combination “J.K. Rowling” and “Harry Potter” already exists in the books table and so our upsert query would update the books table to the following state.

name author quantity
J.K. Rowling Harry Potter 15
Ruskin Bond Angry River 10
Cal Newport Deep Work 20

As you can see, only the quantity of combination “J.K. Rowling” and “Harry Potter” has been changed but the combination “Cal Newport” and “Deep Work” added as a new record into the table.

Learn the fundamentals of PHP 8 (including 8.1, 8.2, and 8.3), the latest version of PHP, and how to use it today with my new book PHP 8 in a Nutshell. It's a no-fluff and easy-to-digest guide to the latest features and nitty-gritty details of PHP 8. So, if you're looking for a quick and easy way to PHP 8, this is the book for you.

Like this article? Consider leaving a

Tip

👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.

Comments?